SQL Server: Handle errors

SQL Server allows to handle errors with TRY ... CATCH blocks, and provides several statements to get information about the error that is being handled.

TRY and CATCH blocks

A TRY block is a portion of TSQL code that is expected to generate errors in some cases. These errors are handled by running the immediately following CATCH block. For example:

BEGIN TRY
    INSERT INTO ... ;
END TRY
BEGIN CATCH
    SELECT 'The INSERT failed with an error' AS message;
END CATCH;

BEGIN TRY
    UPDATE ... ;
END TRY
BEGIN CATCH
    SELECT 'The UPDATE failed with an error' AS message;
END CATCH;

The final ; (after END CATCH) is optional.

Warnings or informational messages with severity lower than 10 are not handled in CATCH blocks.

GOTO cannot be used to enter a TRY block or a CATCH block.

Returning information about an error

When we can an error, we can return information about it using the following functions:

ERROR_NUMBER() – The error code.
ERROR_MESSAGE() – The error message.
ERROR_STATE() – The error SQLSTATE.
ERROR_SEVERITY() – The severity of the error.
XACT_STATE() – Whether a transaction is active, and where it can be committed.
- 0: No active transaction.
- -1: A transaction is active, but the error prevents it from being committed. It can only be rolled back.
- 1: A transaction is active and it can be committed. We can decide whether it’s better to commit it or roll it back.

The following functions tell us where the error occurred:

ERROR_LINE() – The line where the error occurred.
ERROR_PROCEDURE() – The stored procedure, stored function or trigger where the error occurred, or NULL.

An example:

BEGIN TRY
    INSERT INTO ... ;
END TRY
BEGIN CATCH
    IF XACT_STATE() = 1 OR XACT_STATE() = -1
        ROLLBACK TRANSACTION;
    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;
END CATCH;

It is also possible to do something in the CATCH block, and then re-throw the same error using the above functions in combination with RAISERROR(). For more information about this, see SQL Server: Raise errors and warnings.

Reference

See the links to the SQL Server in the text above.