@@ERROR returns an error number of the last statement executed. When the last executed statement completes successfully, this returns 0. In case of any error, the error number will be returned.
Declare @n int, @d int
I am going to show you a common mistake most developers do when using
PRINT. In fact I did so too, and that’s why I am able to write this blog.
Let’s take a look at an example:
Create table tblPrintErr(rowId tinyint)
Insert into tblPrintErr(rowId) values (1)
Insert into tblPrintErr(rowId) values (300) // This statement will generate an error as 300 is not a tinyint
IF @@ERROR = 0
I am forcefully creating an error in order to rollback the transaction, but what really happened was the transaction got committed.
Let’s see what caused for the transaction to commit instead of rollback.
@@ERROR statement next to the insert query is what caused the transaction to commit. When
IF @@ERROR = 0 statement executed, the last executed statement
PRINT @@ERROR, and since this statement executed without error,
@@ERROR holds a value of 0 and the transaction went into commit.
You need to be careful when you use
@@ERROR. In order to avoid the above discussed behaviour,
use a local variable to hold the value of
Declare @Errno int
Insert into tblPrintErr(rowId) values (300)
Set @Errno = @@ERROR
IF @Errno =0
… … … …
… … … …