ERROR HANDLING USING @@ERROR

ERROR HANDLING USING  @@ERROR  

I am explaining here how we can trap error during execution of SQL code. We have used many times global variable in SQL code using @@  one of the global variable is @@ERROR, this is used for  traditional way of trapping error in TSQL. In code in any  case error occurred then it returns values greater than 0.

USE TSQL2012;

GO

Script -1

DECLARE @errorNo INT;

PRINT 1/0;

SET @errorNo = @@ERROR;

IF @errorNo > 0 BEGIN

PRINT 'An error has occurred.'

PRINT @errorNo;

PRINT @@ERROR;

END;

GO

Script -2

DECLARE @errorNo INT;

DROP TABLE testTable;

SET @errorNo = @@ERROR;

IF @errorNo > 0 BEGIN

PRINT 'An error has occurred.'

PRINT @errorNo;

PRINT @@ERROR;

END;

GO

Script--3

DECLARE @errorNo INT;

SET IDENTITY_INSERT Person.ContactType ON;

INSERT INTO Person.ContactType(ContactTypeID,Name,ModifiedDate)

VALUES (1,QA,GETDATE());

SET @errorNo = @@ERROR;

IF @errorNo > 0 BEGIN

PRINT 'An error has occurred.';

PRINT @errorNo;

END;

Even if you don’t use the error trapping, the error prints on the screen in red, and the database engine returns an error message to the client. Notice that the code saves the value of the @@ERROR function before doing anything else. That is because, once another statement runs, the value of @@ERROR changes. Just by accessing it, the value goes back to zero. By saving the value in a local variable, you can check to see whether the value exceeds zero and deal with the error, in this case, just printing the value. You could roll back a transaction or halt the execution of the batch.

Script 1 attempts to divide by zero. Script 2 tries to drop a table that does n’t exist. Script 3 inserts a row into the Person.ContactType table but violates the primary key so the row can’t be inserted.