About @@ERROR System Variable for Rollback

Checking @@ERROR - the Old Way of Handling Errors in T-SQL

SQL Server provides an @@ERROR variable that indicates the status of the last completed SQL statement in a given batch. If a SQL statement is completed successfully, @@ERROR is assigned 0. If, however, an error occurs, @@ERROR is set to the number of the error message.

To see how the @@ERROR variable can be used, imagine that we have a data-driven web application that maintains employee information. Let's assume that our database has Employeesand EmployeePhoneNumbers tables, among others. These two tables share a one-to-many relationship; that is, each Employees record can have an arbitrary number of related records in theEmployeePhoneNumbers table. There might be one for their office phone, one for their pager, one for their cell phone, and so on. Imagine that our database includes a stored procedure,DeleteEmployee, which is comprised of two DELETE statements - one to delete the employee's related phone numbers from the system and one to delete the actual employee record:

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int ) AS -- Delete the Employee's phone numbers DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID -- Delete the Employee record DELETE FROM Employees WHERE EmployeeID = @EmployeeID

Since we want these two delete statements to be atomic and either both fail or both succeed, we need to wrap up these two statements into a transaction. By using a transaction, we can rollback the transaction in the face of an error and undo any changes made since the start of the exception. To accomplish this we might initially try to use the following syntax:

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int ) AS BEGIN TRANSACTION -- Start the transaction -- Delete the Employee's phone numbers DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID -- Delete the Employee record DELETE FROM Employees WHERE EmployeeID = @EmployeeID -- See if there is an error IF @@ERROR <> 0 -- There's an error b/c @ERROR is not 0, rollback ROLLBACK ELSE COMMIT -- Success! Commit the transaction

This stored procedure (it appears) starts a transaction, runs the two DELETE statements, and then checks to see if there was an error. If there was one, it rolls the transaction back, else it commits the transaction. I say "it appears" because this syntax, while legal, is semantically incorrect because the @@ERROR variable is set after every SQL statement. Therefore, if the firstDELETE statement has an error the @@ERROR variable will be set to its error number. Then, the second DELETE will execute. If this second DELETE succeeds, @@ERROR will be set back to 0, in which case the transaction will be committed even though there was a problem with the first statement! Whoops!

Instead, a check must be made after every SQL statement to see if there has been an error. If so, the transaction must be rolled back and the stored procedure exited. This can lead to bulky scripts as a stored procedure with, say, five statements will have five checks against the @@ERROR variable. And if you forget to cut and paste a check in for a particular statement you're opening yourself up to a potential problem.

For more information on transactions and the @@ERROR syntax used for checking for errors and rolling back as needed, see Managing Transactions in SQL Server Stored Procedures.