Using Try Catch in SQL Server 2005


The try catch structure in SQL Server 2005 is one of the best additions to the TSQL language.  This allows for true error trapping and hadling as opposed to inspecting the @@ERROR variable in the rollback section of a transaction.  The syntax and structure for a TSQL try catch is as follows:

BEGIN TRANSACTION
BEGIN TRY
   INSERT INTO TableA VALUES ('Value1', 'Value2', GETDATE(), SUSER_SNAME())
   COMMIT
END TRY
BEGIN CATCH
   SELECT ERROR_NUMBER() as ErrorNumber
   ROLLBACK
END CATCH

Notice the user of ERROR_NUMBER() instead of @@ERROR?  The following article details how you can use this apprach to resolve deadlock situations in your applications:


http://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsqlpro05/html/sp05e10.asp

Comments (1)
  1. ecarroll says:

    Do you still have to Commit Transaction or is Commit enough with 2005?

Comments are closed.

Skip to main content