SQL Server TRY-CATCH error handling

Thanks to four very talented and inquisitive developers who were taking my Implementing a Microsoft SQL Server 2005 Database class last week, I was encouraged to delve a little deeper into SQL Server’s TRY-CATCH error handling structure.  (You know who you are, and thank you….)

First implemented in SQL Server 2005, the idea was to introduce structured exception and error handling into SQL Server.  The general idea goes something like this:

BEGIN TRY
      <SQL Statement(s) to attempt data modification go here>
END TRY
BEGIN CATCH
      <SQL Statements for Custom Error Handling Go Here>
END CATCH

If the code contained between the BEGIN TRY and END TRY statements runs successfully, then execution jumps to the next statement after the END CATCH statement.  If the code contained between the BEGIN TRY and END TRY statements produces an error with a severity level greater than 10 that are not fatal to the connection.

If you are familiar with the TRY-CATCH block in other Microsoft languages such as Visual Basic, you are used to seeing execution jump to the code contained in the CATCH block immediately after the error (exception) is thrown.  Unfortunately for consistency’s sake, this is not the case for SQL Server.

In SQL Server 2005 and SQL Server 2008, all code between the BEGIN TRY and END TRY executes before the error states are checked.  This means you can get yourself into trouble if you “assume” that it behaves like the TRY-CATCH block in other languages.  For example:

BEGIN TRY
     BEGIN TRAN
          <SQL Statement(s) to attempt data modification go here>
     COMMIT TRAN
     BEGIN TRAN
          <SQL Statements(s) to attempt a second data modification go here>
     COMMIT TRAN
END TRY
BEGIN CATCH
      <SQL Statements for Custom Error Handling Go Here>
END CATCH

If SQL Server’s TRY-CATCH behaved like Microsoft’s complied languages, then if the first transaction failed, execution would immediately jump to the CATCH block.  However, in SQL Server, both transactions would run, and while a failure of either would cause the CATCH block to execute, you could end up with one transaction committed you did not expect to be committed.

While SQL Server’s version of the TRY-CATCH is restricted to a single batch, it is not restricted to a single transaction.  If you are going to use the TRY-CATCH in SQL Server, you will want to make sure you only have one transaction or logical unit of work in the TRY block.

 Hope you find this useful;

 James

Leave a Reply

You must be logged in to post a comment.