Usage of @@Error within transaction

@@ERROR returns error number of the last statement executed. When the last executed statement completed successfully, this returns 0.
In case of any error, the error number will be return.

Example:

Declare @n int, @d int
Set @n=10
Set @d=0
Select @n/@d
PRINT @@ERROR 

I am going to show you a common mistake most of the developer does when using @@ERROR and PRINT, In fact I did so, that’s why I am able write this blog.

Let’s take a look at an example

Create table tblPrintErr(rowId tinyint)
Begin Tran
Insert into tblPrintErr(rowId) values (1)
PRINT @@ERROR
Insert into tblPrintErr(rowId) values (300) // Error as 300 is not a tinyint
PRINT @@ERROR
IF @@ERROR = 0
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END

I am forcefully creating an error in order to roll back the transaction, but what really happened was the transaction got committed. Let’s see what caused for the transaction to commit instead of roll back.

PRINT @@ERROR statement next to the insert query is what caused the transaction to commit. When IF @@ERROR = 0 statement executed, the last executed statement was PRINT @@ERROR, since it executed without error, @@ERROR holds value of 0 and transaction entered into commit.

You need to be careful, when you use @@ERROR. In order to avoid the above discussed behavior. Use local variable to hold the value of @@ERROR.

Example:

Declare @Errno int
Insert into tblPrintErr(rowId) values (300)
Set @Errno = @@ERROR
.
.
.
.
IF @Errno =0
BEGIN
… … … …
END
ELSE
BEGIN
… … … …
END