Error Handling in SQL Server 2005

TRY…CATCH construct is used for error handling in side T-SQL code. A TRY…CATCH construct consists of 2 blocks. One is TRY block that is immediately followed by a CATCH block. TRY…CATCH construct can be nested. When an error condition is identified in TRY block, the control is immediately transferred to CATCH block where the error can be handled.

The following code represents a basic TRY…CATCH construct.

BEGIN TRY

SELECT 1/0;

END TRY

BEGIN CATCH

      SELECT ERROR_MESSAGE();

END CATCH

Error Functions:

The following error functions can be used inside a TRY…CATCH construct to get the information about the error that is occurred.

ERROR_NUMBER() returns the error number.

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.

ERROR_SEVERITY() returns the error severity.

ERROR_STATE() returns the error state number.

ERROR_LINE() returns the line number inside the routine that caused the error.

ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

TRY…CATCH with RAISERROR

RAISERROR can be used inside a TRY…CATCH construct for error handling. RAISERROR generates error message using user defined messages that is stored inside sys.messages catalog view or build a message dynamically.

The following code returns an error message and the WITH LOG statement causes the error to be logged into windows application log. Using event viewer, you can view the log.

BEGIN TRY

    RAISERROR ('Error raised in TRY block.', 16,10) WITH LOG;

END TRY

BEGIN CATCH

    SELECT Error_Message() as Message_text,

               Error_Severity() as Severity,

               Error_State() as State

END CATCH

Questions:

1. What types of error cannot be handled by TRY…CATCH construct. Choose all correct answers from the following:

a. All DDL statement errors

b. All DML statement errors

c. Divide by Zero error

d. Syntax errors that prevent a batch from executing.

Answers:

1. d.