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.
Block of T-SQL code that can be an issue
--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
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.
http://www.sql-server-performance.com/articles/per/deadlock_sql_2005_p1.aspx TRY/CATCH Helps to Resolve Deadlocks