Error handling in Transact SQL

Error handling is a fundamental aspect in managing Transact SQL code, which allows us to control our code flow and detect problems and errors making it easier for us to resolve them.

There are flow control structures specially designed for Error handling. In this section we will focus on TRY...CATCH and THROW.

TRY…CATCH

TRY...CATCH is a flow control structure that allows us to detect and catch errors. The essential operation is simple:

-TRY: encloses a series of Transact SQL statements that will attempt to execute.

• CATCH: collects any error that occurs in the TRY, executing the statements of this CATCH block, which are usually intended to provide information about the error produced.

Regarding the syntax, it is the following:

BEGIN TRY
     [code]
END TRY
BEGIN CATCH
    [code]
END CATCH

There are a few things to keep in mind about TRY...CATCH

• It can be used inside stored procedures.

• TRY...CATCH statements can be nested.

• TRY must always be followed immediately by its corresponding CATCH block. Any statement between END TRY and BEGIN CATCH will cause a syntax error.

• It does not allow to encompass several lots. For example, it cannot contain more than one BEGIN...END block or IF...ELSE construction.

ERROR function

There are a series of functions in charge of returning information about the errors that have occurred, which are often used within the CATCH block.

• ERROR_NUMBER(): returns the error number.

• ERROR_SEVERITY(): returns the severity of the error.

• ERROR_STATE(): returns the error state number.

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

• ERROR_LINE(): returns the line number within the routine that caused the error.

• ERROR_MESSAGE(): returns the full text of the error message.

Example

To exemplify how TRY...CATCH works in a simple way, we proceed to try dividing a number by zero within a TRY...CATCH block to capture and return error information:

BEGIN TRY
    SELECT 1/0;
END TRY
BEGIN CATCH
    SELECT
         ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO

THROW

THROW allows us to generate our own exceptions and return them in the CATCH block of a TRY...CATCH construct. This way we can handle a custom **error handling**.

The syntax is:

THROW  (error_number | variable,
        message | variable ,
        state | variable )

THROW arguments

As we can see from its syntax, the THROW statement uses three main arguments in its declaration:

1. error_number: Indicates the number of the error. It will be INT data type and its value must be between 50000 and 2147483647 (both included).

2. message: returns the full text of the error message that we specify.

3. state: returns the error status number. It will be of type TINYINT with a value between 0 and 255 (both included).

Example

To exemplify how THROW works in a simple way, we proceed to try to divide a number by zero within a TRY...CATCH block to capture and return the custom information of the generated error:

BEGIN TRY
     SELECT 1/0;
END TRY
BEGIN CATCH
     THROW 50100, 'The mathematical operation is wrong.',1;
END CATCH;
GO