Try Catch

TRY...CATCH is a standard method of trapping and handling errors.

The basic idea is that if SQL Server encounters any errors when it tries to execute a

block of code, it will stop execution of the TRY block and immediately jump to the CATCH block to handle the error:

BEGIN TRY

<SQL code>;

END TRY

BEGIN CATCH

<error handling code>;

END CATCH;

If the TRY block of code executes without any error, then the CATCH code is never executed, and execution resumes after the CATCH block:

BEGIN TRY

SELECT ‘Try One’;

RAISERROR (’Simulated Error’, 16, 1);

Select ‘Try Two’;

END TRY

BEGIN CATCH

SELECT ‘Catch Block’;

END CATCH;

SELECT ‘Post Try’;

Result:

---------

Try One

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

Catch Block

-----------

Post Try

(1 row(s) affected)

Walking through this example, SQL Server executes the TRY block until the RAISERROR’s simulated error, which sends the execution down to the CATCH block. The entire CATCH block is executed.

Following execution of the CATCH block, execution continues with the next statement, SELECT ‘Post Try’.

The T-SQL compiler treats the END TRY ... BEGIN CATCH combination as a single contiguous command. Any other statements, a batch terminator (go), or a statement terminator (;) between these two commands will cause an untrapped error. END TRY must be followed immediately by a BEGIN CATCH.

Catch block:

When an error does occur, the best way to trap and handle it is in the CATCH blocks. Within the CATCH block, you want to do the following:

1. If the batch is using logical transactions (BEGIN TRAN/COMMIT TRAN), then, depending on the error and situation, the error handler might need to roll back the transaction. If this is the case, I recommend rolling back the transaction as the first action so that any locks the transaction might be holding are released.

2. If the error is one that the stored procedure logic detects, and it’s not a SQL Server error, then raise the error message so that the user or front-end application is informed.

3. Optionally, log the error to an error table.

4. Terminate the batch. If it’s a stored procedure, user-defined function, or trigger, then terminate it with a RETURN command.

When an error occurs in the TRY block and execution is passed to the CATCH block, the error information is also passed to the CATCH block. The information may be examined using the error functions listed in below Table.

These functions are designed specifically for the CATCH block. Outside a CATCH

block, they will always return a null value.

Catch Functions

Error Function Returns

Error_Message() The text of the error message

Error_Number() The number of the error

Error_Procedure() The name of the stored procedure or trigger in which the error occurred

Error_Severity() The severity of the error

Error_State() The state of the error

Error_Line() The line number within the batch or stored procedure that generated

the error

Xact_State() Whether the transaction can be committed

These CATCH functions retain the error information of the error that fired the CATCH block. They may be called multiple times and still retain the error information.

The following sample demonstrates a CATCH block using the CATCH functions and a RAISERROR to report the error to the client. The contents of the error functions are being passed to variables so a custom error string can be assembled for the RAISERROR:

BEGIN

BEGIN TRY

SELECT 10/0

END TRY

BEGIN CATCH

DECLARE

@Error_Severity INT,

@Error_State INT,

@Error_Number INT,

@Error_Line INT,

@Error_Message VARCHAR(245);

SELECT

@Error_Severity = ERROR_SEVERITY(),

@Error_State = ERROR_STATE(),

@Error_Number = ERROR_NUMBER(),

@Error_Line = ERROR_LINE(),

@Error_Message = ERROR_MESSAGE();

RAISERROR ('Msg %d, Line %d: %s',

@Error_Severity,

@Error_State,

@Error_Number,

@Error_Line,

@Error_Message);

END CATCH;

END

Nested try/catch and rethrown errors

Any error (or RAISERROR event) will bubble up through every layer of stored procedures until it’s caught by a try/catch block or it reaches the client. Visualizing the call stack — the stack of procedures that have executed or called other stored procedures — it’s possible for lower level, or nested, stored

procedures to use this principle to send, or rethrow, errors to higher-level stored procedures in the call stack.

Try/catch blocks can easily be nested even if the nesting is unintentional. If one stored procedure calls another stored procedure and both procedures are well written, with try/catch blocks, then not only are the stored procedures nested, but the try/catch blocks are nested too.

In the following example, the TopProc will execute, or call, the CalledProc. A divide by zero error in CalledProc causes the code to jump to the CATCH block. The catch block will issue a RAISERROR.

The TopProc will receive the error that was raised by the CalledProc. It sees the error as any other type of error and therefore jumps down to its CATCH block. The RAISERROR in the TopProc is executed,and it too raises an error. This time the raised error is seen by the client, in this case Management Studio:

CREATE PROC TopProc

AS

BEGIN TRY

EXEC CalledProc

END TRY

BEGIN CATCH

RAISERROR (’TopProc Raiserror’,16,1)

END CATCH

GO

CREATE PROC CalledProc

AS

BEGIN TRY

SELECT 3/0

END TRY

BEGIN CATCH

RAISERROR (’CalledProc Raiserror’,16,1)

END CATCH

Go

EXEC TopProc

Result:

Msg 50000, Level 16, State 1, Procedure TopProc, Line 7

TopProc Raiserror

A TRY…CATCH construct catches all execution errors that have a severity higher than 10 that do not close the database connection.

A TRY…CATCH construct cannot span multiple batches. A TRY…CATCH construct cannot span multiple blocks of Transact-SQL statements. For example, a TRY…CATCH construct cannot span two BEGIN…END blocks of Transact-SQL statements and cannot span an IF…ELSE construct.

If there are no errors in the code that is enclosed in a TRY block, when the last statement in the TRY block has finished running, control passes to the statement immediately after the associated END CATCH statement. If there is an error in the code that is enclosed in a TRY block, control passes to the first statement in the associated CATCH block. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed back to the statement that called the stored procedure or fired the trigger.

When the code in the CATCH block finishes, control passes to the statement immediately after the END CATCH statement. Errors trapped by a CATCH block are not returned to the calling application. If any part of the error information must be returned to the application, the code in the CATCH block must do so by using mechanisms such as SELECT result sets or the RAISERROR and PRINT statements.

TRY…CATCH constructs can be nested. Either a TRY block or a CATCH block can contain nested TRY…CATCH constructs. For example, a CATCH block can contain an embedded TRY…CATCH construct to handle errors encountered by the CATCH code.

Errors encountered in a CATCH block are treated like errors generated anywhere else. If the CATCH block contains a nested TRY…CATCH construct, any error in the nested TRY block will pass control to the nested CATCH block. If there is no nested TRY…CATCH construct, the error is passed back to the caller.

TRY…CATCH constructs catch unhandled errors from stored procedures or triggers executed by the code in the TRY block. Alternatively, the stored procedures or triggers can contain their own TRY…CATCH constructs to handle errors generated by their code. For example, when a TRY block executes a stored procedure and an error occurs in the stored procedure, the error can be handled in the following ways:

·         If the stored procedure does not contain its own TRY…CATCH construct, the error returns control to the CATCH block associated with the TRY block that contains the EXECUTE statement.

·         If the stored procedure contains a TRY…CATCH construct, the error transfers control to the CATCH block in the stored procedure. When the CATCH block code finishes, control is passed back to the statement immediately after the EXECUTE statement that called the stored procedure.

GOTO statements cannot be used to enter a TRY or CATCH block. GOTO statements can be used to jump to a label inside the same TRY or CATCH block or to leave a TRY or CATCH block.
The TRY…CATCH construct cannot be used in a user-defined function.