SQL Server 2005 introduced a new error handling convention called the TRY/CATCH block, which allows for more programmable error trapping in T-SQL code. This feature brings SQL Server in line with other programming languages, such as Microsoft Visual C# and C++. In addition to the TRY/CATCH block, SQL Server 2005 also introduced new system functions for returning error codes and messages when an error occurs in your T-SQL code.

Coding a TRY/CATCH Block

The TRY/CATCH block in T-SQL is similar to the error handling blocks in other programming languages. It consists of a TRY block, where the code that may cause an error is placed, followed by a CATCH block, where the actions to be taken when an error occurs are defined. Here is the syntax for a TRY/CATCH block:

BEGIN TRY
  -- code that may cause an error
END TRY
BEGIN CATCH
  -- actions to be taken when an error occurs
END CATCH

The TRY or CATCH block can contain a single T-SQL statement or a series of statements. The CATCH block must immediately follow the TRY block, and the TRY/CATCH block cannot span multiple batches or an IF/ELSE statement.

Processing Rules for a TRY/CATCH Block

When an error occurs within the TRY block, control is passed to the first statement within the CATCH block. Any statements in the TRY block that follow the statement causing the error are skipped. For example:

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'

In this example, the SET statement tries to divide by zero, causing an error. The PRINT statement immediately following the SET statement is skipped, and the first statement within the CATCH block is executed, displaying the message “Error Detected”.

Nesting TRY/CATCH Blocks

TRY/CATCH blocks can be nested to provide more granular error handling. This can be useful when dealing with complex operations that involve multiple steps. Here is an example of nested TRY/CATCH blocks:

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

In this example, if the delete operation on the GrandParent table fails, the code moves to the CATCH block and attempts to delete the Parent record. If that operation fails as well, the code moves to the nested CATCH block and attempts to delete the Child record. Each delete operation is supported by a separate TRY/CATCH block.

Available System Error Functions

SQL Server 2005 introduced several new system error functions that can be used within a CATCH block to retrieve error information. These functions include:

• ERROR_NUMBER(): Returns the error 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.

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

Limitations of the TRY/CATCH Block

Not all errors can be trapped by a TRY/CATCH block. Compile errors, deferred name resolution errors, and errors caused by a KILL command or broken client connections are not captured by the TRY/CATCH block. For these types of errors, SQL Server 2005 passes control back to the application immediately without executing any CATCH block code.

Transaction Handling in the TRY Block

If an error occurs within a TRY block that invalidates a transaction, the transaction becomes uncommittable. In this state, only read-type statements or a ROLLBACK TRANSACTION statement can be executed. The XACT_STATE function can be used to determine the transaction state.

Conclusion

The TRY/CATCH block and system error functions introduced in SQL Server 2005 greatly enhance error handling in T-SQL code. By using these features, developers can more effectively handle errors and retrieve detailed error information. As you continue to write T-SQL code in SQL Server, consider incorporating the TRY/CATCH block and system error functions to improve your error processing logic.