Exception Handling in SQL Server 2000 and 2005

New Error Handling Feature in SQL Server SQL Server 2005

SQL Server 2005 has made a major improvement in error handling inside of T-SQL transactions. Besides retaining all of the features found in SQL Server 2000, you can now catch Transaction Abort Errors using the TRY/CATCH model without any loss of the transaction context. Take a look at the code below:

In the above example I have used one TRY/CATCH block and no use of the @@error variable. This increases the readability of the code and much simple to write. The fundamental rule to use TRY/CATCH is that the CATCH block should immediately follow the TRY block.

What I have said this far applies to when XACT_ABORT is OFF, which is the default. When you issue SET XACT_ABORT ON, the most of the statement termination errors instead become batch abortions errors. Observe the code below:

Here’s the results of running the above code:

(1 row(s) affected)

(1 row(s) affected)

Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__SQL Server 2005Test__5E1FF51F'. Cannot insert duplicate key in object 'SQL Server 2005Test'.
Msg 3903, Level 16, State 1, Line 9
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

In sample code 4, as the XACT_ABORT was OFF, the execution of the batch insert did not stop even though an error occurred at line 6. Only the statement where the error occurred was terminated, this is called as Statement Termination. Wherein Sample Code 5, the XACT_ABORT is made ON, and the batch is terminated on error, i.e. the statements after statement 7 are terminated and hence the statement 8 could not insert the valid values.

Whether you are running SQL Server 2000, or are getting to know SQL Server 2005, learning how to write good error-trapping code is an important skill to master.

Amol Kulkarni is an employee of Tata Consultancy Services (TCS), Hyderabad, India.

Published with the explicit written permission of the author. Copyright 2005