Try/Catch Block in SQL Server 2005

Server 2005 now supports a more programmable error trapping convention in T-SQL code. This error handling is known as the TRY/CATCH block. The TRY/CATCH block allows T-SQL to handling errors like other programming languages, such as Microsoft Visual C# and C++. In addition to the TRY/CATCH block, Microsoft has expanded the set of available system functions in SQL Server 2005 for returning error codes and messages when an error occurs in your T-SQL code. This article will explore the new TRY/CATCH block and explain the new system functions for returning error codes and messages.

Coding a TRY/CATCH Block within T-SQL

Coding a TRY/CATCH block in T-SQL is similar to the TRY/CATCH blocks of other languages. The TRY/CATCH block consists of a block of code identified as a TRY block, followed by another block of code known as a CATCH block. If an error is encountered in the TRY block, then the CATCH block is executed to determine what actions should be taken to deal with the error encountered. Keep in mind that only errors with a severity greater than 10 that do not terminate their database connection are caught in the TRY/CATCH block. Here is the syntax for a TRY/CATCH block:

BEGIN TRY

     {  sql_statement |

 statement_block  }

END TRY

BEGIN CATCH

     {  sql_statement |

 statement_block }

END CATCH

The TRY or CATCH block can contain a single T-SQL statement or a series of statements. The CATCH block must follow immediately after the TRY block, otherwise you will receive a syntax error. The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.

Processing Rules for a TRY/CATCH Block

When a TRY block encounters an error, control is passed to the first statement within the CATCH block. If there are statements in the TRY block that follow the T-SQL statement that caused the error they will not be executed, but will instead be skipped over. To demonstrate this, run the following code:

BEGIN TRY

  DECLARE @X INT

  -- Divide by zero to generate Error

  SET @X = 1/0

  PRINT 'Command after error in TRY block'

END TRY

BEGIN CATCH

  PRINT 'Error Detected'

END CATCH

PRINT 'Command after TRY/CATCH blocks'

When I run this code on my server I get the following output:

Error Detected

Command after TRY/CATCH blocks

In the code above, the SET statement tries to divide by zero, which is an error. Because the SET statement received an error, the PRINT statement that immediately follows the SET statement is not executed. Therefore, this skipped PRINT statement does not display a message in the generated output. Instead, the first statement within the CATCH block, a PRINT statement, displays the message “Error Detected”.

If all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block, but instead skips over the CATCH block and executes the first statement following the END CATCH statement. To see this in action take the code above and remove, or comment out the SET statement that does the divide by 0, and then execute. When you do this, you will see that the PRINT ‘Error Detected’ statement is not executed, but the PRINT statement within the TRY block is executed, as well as the PRINT statement after the TRY/CATCH block.

Nesting TRY/CATCH Blocks

TRY/CATCH blocks can be nested. Doing this helps you to more finely control your error handling. Below is an example of nesting TRY/CATCH blocks. This example deletes records from a set of tables that have a grandparent, parent, child relationship:

Begin TRY

  delete from GrandParent where Name = 'John Smith'

  print 'GrandParent deleted successfully'

End Try

Begin Catch

   Print 'Error Deleting GrandParent Record'

   Begin Try

     delete from Parent where GrandParentID =

     (select distinct ID from GrandParent where Name = 'John Smith')

     Print 'Parent Deleted Successfully'

   End Try

   Begin Catch

     print 'Error Deleting Parent'

     Begin Try

       delete from child where ParentId =

     (select distinct ID from Parent where GrandParentID =

     (select distinct ID from GrandParent where Name = 'John Smith'))

       print 'Child Deleted Successfully'

     End Try

     Begin Catch

       Print 'Error Deleting Child'

     End Catch

   End Catch

 End Catch

This code first tries to delete the grandparent record. If the delete of the grandparent record is unsuccessful then it tries to delete the parent record. If the parent record cannot be deleted then the child record is deleted. Each delete command is supported by a different, nested TRY/CATCH block.

Available System Error Functions

SQL Server 2005 has a number of new system error functions for returning error information. These new functions can be used in a CATCH block to help identify why an error occurred. Below is a list of the error functions available to CATCH blocks in SQL Server 2005, and the information each of these functions return:

ERROR_NUMBER(): Returns a number associated with the error.

ERROR_SEVERITY(): Returns the severity of the error.

ERROR_STATE(): Returns the error state number associated with the error.

ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger in which the error occurred.

ERROR_LINE(): Returns the line number inside the failing routine that caused the error.

ERROR_MESSAGE(): Returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

These error functions can only be used within the scope of a CATCH block. If these functions are used outside a CATCH block, they will return a NULL value.

Let me demonstrate one way you can use these functions to return error information. Below is a slightly modified TRY/CATCH block of code, similar to my divide by zero example above:

BEGIN TRY

  DECLARE @X INT

  -- Divide by zero to generate Error

  SET @X = 1/0

  PRINT 'Command after error in TRY block'

END TRY

BEGIN CATCH

  PRINT 'Error Detected'

  SELECT ERROR_NUMBER() ERNumber,

         ERROR_SEVERITY() Error_Severity,

         ERROR_STATE() Error_State,

         ERROR_PROCEDURE() Error_Procedure,

         ERROR_LINE() Error_Line,

         ERROR_MESSAGE() Error_Message

END CATCH

PRINT 'Command after TRY/CATCH blocks'

In this example, I added a SELECT statement to my CATCH block. This added SELECT statement displays the results returned for each of these available system error functions. When I execute this code on my server, I get results similar to the following:

Error Detected

Err_Num Err_Sev Err_State Err_Proc             Err_Line  Err_Msg

------- ------- --------- -------------------- --------- --------------------------------

8134        16          1 NULL                 4        Divide by zero error encountered.

What Errors Are Not Trapped by a TRY/CATCH Block:

Not all errors can be trapped by a TRY/CATCH block. The first types of errors that are not trapped are compile errors, like a syntax error within a batch of T-SQL statements. Another type of error is deferred name resolution errors created by statement level recompilations. If a process is terminated by a KILL command then a TRY/CATCH block does not capture this error. Client interrupt requests or broken client connections are also not trapped by the TRY/CATCH block. For errors that are not trapped, SQL Server 2005 passes control back to the application immediately, without executing any CATCH block code.

How the TRY Block Affects a Transaction

If an error occurs within a TRY block that causes a transaction to be become invalided then the transaction becomes uncommittable. Once a transaction becomes uncommittable only read type statements or a ROLLBACK TRANSACTION statement can be executed. The XACT_STATE function can be used to determine the transaction state. If the XACT_STATE function returns a -1 then the current active transaction is known as uncommittable. If the XACT_STATE function returns a 0 then the current batch does not have an active transaction, but if this function returns a 1 then the batch has a current transaction that can still be committed.

Conclusion

SQL Server 2005 has greatly enhanced the way you can handle error processing within in your T-SQL code. The TRY/CATCH block makes it easier to code error logic. More information about specific errors can now be returned with the new system error functions. As you move forward with writing T-SQL code in SQL Server 2005, consider using the TRY/CATCH and the system error functions to improve your error processing logic.