Like other programming languages, T-SQL provides a sophisticated mechanism that captures and handles errors during execution. The mechanism for handling errors during execution includes the object-oriented-programming-style TRY…CATCH construct. When writing Transact-SQL batches and programmable objects, we wrap the Transact-SQL statements to be executed within a TRY block, and at runtime, if an error occurs, control is sent to the CATCH block. We enclose error-handling code within the CATCH block. The syntax for the TRY…CATCH construct is as follows:

BEGIN TRY
{ sql_statement |statement_block}
END TRY
BEGIN CATCH
[{ sql_statement |statement_block}]
END CATCH

Only errors with severity between 11 and 19 cause the CATCH block to execute. SQL Server treats errors with lower severity as informational messages. Errors with severity 20 or higher usually terminate the connection. If they do terminate the connection, SQL Server does not execute the Transact-SQL code within the CATCH block. If they do not terminate the connection, SQL Server executes the Transact-SQL code within the CATCH block.

You can use the following scalar functions within the CATCH block to retrieve the information about the error that caused the CATCH block to execute:

You can use THROW with the TRY block to raise an exception and transfer execution to the CATCH block. The THROW statement must end with a semicolon (;). The syntax for the THROW statement is as follows:

THROW [{ error_number | @local_variable },
{ message | @local_variable },
{ state | @local_variable }] [ ; ]

We use the RAISERROR statement to instruct SQL Server to send an error to a client application. We typically use the RAISERROR statement for user-defined errors. The syntax for the RAISERROR statement is as follows:

RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

The following sections discuss a few examples of the TRY…CATCH construct.

An example of TRY...CATCH

The following code snippet illustrates a simple TRY…CATCH block:

BEGIN TRY
    SELECT  50 / 0
END TRY
BEGIN CATCH
    SELECT  @@ERROR AS [@@ERROR]
    SELECT  ERROR_NUMBER() AS [ERROR_NUMBER] ,
            ERROR_MESSAGE() AS [ERROR_MESSAGE]
END CATCH

Executing this will return the output shown in the following screenshot:

An example of TRY...CATCH with THROW

The following code snippet illustrates a TRY…CATCH expression with a THROW block:

BEGIN TRY
    SELECT  50 / 0
END TRY
BEGIN CATCH
    THROW;
END CATCH

Executing this will return the output shown in the following screenshot:

An example of TRY...CATCH with RAISERROR

The following code snippet illustrates a TRY…CATCH expression with a RAISERROR block:

BEGIN TRY
    SELECT  50 / 0
END TRY
BEGIN CATCH
    RAISERROR (N'Oops, a divide-by-zero error occurred.', 16, 1) WITH NOWAIT
END CATCH

Executing this will return the output shown in the following screenshot: