TRY/CATCH - The Great Way to Handle Exceptions in SQL 2005/8

This is based on the original post in 'Franglais'.

The goal of this post is to explain how to handle errors thanks to a useful T-SQL functionality, available since SQL 2005 onwards, that developers are already used to – and similar to how exceptions are handled in the Visual Studio environment.

If you are used to using @@error raiserror in previous versions of SQL Server, then the probably the best thing for you to use now is TRY/CATCH. Not to be used everywhere, just when you have to run multiple inserts/updates that are critical and that you anticipate errors for that code. For just a single insert/update within a proc, then just use begin / end and do not go crazy with it everywhere.

To use Try/Catch, you'll group your first block of sometimes iffy exception code within the Begin Try/End Try. The second block will be your Begin Catch/End Catch.

Begin Try
Block of T-SQL code that can be an issue
End Try
--when there is a problem with the above (as soon as there is an error):
Begin Catch --
Bloc du code T-SQL -- useful sysfuntions are available, VS programmers like this...
-- Error_Number/Severity/State/Procedure/Line/Message() here use these functions

-- to throw back info to the application as necessary
End Catch

The typical method of control is the Begin Transaction/Commit, but you can also take advantage of Try/Catch block. An exception will not automatically cause the transaction to rollback - this is a common myth...things will keep running afterwards and a commit will happen with the part of code you really needed to run before. One can also use XACT_ABORT whenever you are not using TRY/CATCH and are dealing with transactions (thanks to Adam Machanic MVP for clarifying this in his book Expert SQL Server 2005 Development page 62-72). In other words, use only one of the two at the same time: either set XACT_ABORT ON or your Try/Catch block.

Nota Bene: Compilation errors or syntax are not handled in the Try/Catch block, so make sure to sort those out before using this functionality. It's used to handle transactions that are prone to exceptions. If you have logical errors, then TRY/CATCH is not your saviour.

References:
http://msdn.microsoft.com/en-us/library/ms175976.aspx
http://www.sql-server-performance.com/articles/per/deadlock_sql_2005_p1.aspx TRY/CATCH Helps to Resolve Deadlocks
http://www.databasejournal.com/features/mssql/article.php/3587891
http://blog.sqlauthority.com/2007/04/11/sql-server-2005-explanation-of-trycatch-and-error-handling/