how to know if insertion is successful ?

Question

how to know if store procedure insertion is successful  ? Please advise.   Thanks

ALTER PROCEDURE [dbo].[AddTrainer]
	
@EmployeeID varchar(200) ,
@EmployeeName varchar(200),
@City varchar (500),
@Area varchar (30),
@Title  varchar (200)	
	
AS
BEGIN
insert into [ui].[tblNationalTESAdjunt] ( [EmpID]
      ,[EmpName]
      ,[EmpLocation]
      ,[EmpArea]
      ,[EmpJobTitle]   ) values (@EmployeeID,@EmployeeName,@City,@Area,@Title  )
END

Answer 1

You can check the @@ROWCOUNT right after insert. If it's more than 0, then the insert succeeded. Also, if @@ERROR = 0 after insert, it was successful.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

I have more than one record, I guess I have to use @@ERROR. Thanks

Answer 3

I got to check @@error from the client side ?

Answer 4

No, check it in T-SQL although if the insert will result in error, most likely the error will be propagated into the client. Also, INSERT will either succeed or fail, so if @@ROWCOUNT>0 it succeeds, if @@ERROR <> 0 it fails. IF @@ROWCOUNT = 0 it can be still successful, just no rows satisfy the criteria.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 5

SQL Server will raise an error to the client if an insert fails, which can be caught in the calling application code.  In order to detect the failed insert in T-SQL, the proc can either examine @@ERROR for a non-zero value or use structured error handling with a TRY/CATCH block.  In the event of an error, return a non-zero error code which can be examined by the calling T-SQL code.  Below is an example of each method.

CREATE PROC dbo.usp_insert
	@SomeValue int
AS
INSERT INTO dbo.SomeTable VALUES(@SomeValue);
RETURN @@ERROR;
GO

CREATE PROC dbo.usp_insert
	@SomeValue int
AS
DECLARE @ReturnCode int;
BEGIN TRY
	INSERT INTO dbo.SomeTable VALUES(@SomeValue);
END TRY
BEGIN CATCH
	DECLARE
		@ErrorNumber int
		,@ErrorMessage nvarchar(2048)
		,@ErrorSeverity int
		,@ErrorState int
		,@ErrorLine int;

	SELECT
		@ErrorNumber =ERROR_NUMBER()
		,@ErrorMessage =ERROR_MESSAGE()
		,@ErrorSeverity = ERROR_SEVERITY()
		,@ErrorState =ERROR_STATE()
		,@ErrorLine =ERROR_LINE();

	RAISERROR('Error %d caught at line %d: %s'
		,@ErrorSeverity
		,@ErrorState
		,@ErrorNumber
		,@ErrorLine
		,@ErrorMessage);


	RETURN @ErrorNumber;

END CATCH

RETURN @@ERROR;
GO

To detect the error in the calling script:

DECLARE @ReturnCode int;
EXECUTE @ReturnCode = dbo.usp_insert 1;
IF @ReturnCode <> 0 ...

Note that the calling code could also implement structured error handling to detect an error.

---

Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/