Programming Microsoft® SQL Server™ 2008

Exception Handling in Transactions

SQL Server offers major improvements in error handling inside T-SQL transactions. As of SQL Server 2005, you can catch T-SQL and transaction abort errors using the TRY/CATCH model without any loss of the transaction context. The only types of errors that the TRY/CATCH construct can’t handle are those that cause the termination of your session (usually errors with severity 21 and above, such as hardware errors). The syntax is shown here:

BEGIN TRY
  --sql statements
END TRY
BEGIN CATCH
  --sql statements for catching your errors
END CATCH

If an error within an explicit transaction occurs inside a TRY block, control is passed to the CATCH block that immediately follows. If no error occurs, the CATCH block is completely ...