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