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:
https://msdn.microsoft.com/sql/default.aspx?pull=/library/en-us/dnsqlpro05/html/sp05e10.asp
Comments
- Anonymous
October 26, 2005
Do you still have to Commit Transaction or is Commit enough with 2005?