Click here to Skip to main content
Click here to Skip to main content

Usage of @@Error within a transaction

, 29 Jan 2013
Rate this:
Please Sign up or sign in to vote.
Using @@Error within a transaction.

@@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.

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 developers do when using @@ERROR and 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)
Begin Tran
Insert into tblPrintErr(rowId) values (1)
PRINT @@ERROR
Insert into tblPrintErr(rowId) values (300) // This statement will generate an 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 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.

PRINT @@ERROR statement next to the insert query is what caused the transaction to commit. When the IF @@ERROR = 0 statement executed, the last executed statement was 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 @@ERROR.

Example:

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

DiscoverSQL
Technical Lead
India India
No Biography provided

Comments and Discussions

 
QuestionGood One PinmemberSreenath Kalahasti5-Feb-13 11:47 
Questionxact_abort on or off PinmemberMember 979571831-Jan-13 2:19 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140709.1 | Last Updated 29 Jan 2013
Article Copyright 2013 by DiscoverSQL
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid