SpDev Install free

Error handling in SQL Server: TRY...CATCH, THROW and XACT_STATE

How TRY...CATCH works in T-SQL, the errors it can't catch, THROW vs RAISERROR, doomed transactions and XACT_STATE, and a CATCH block you can copy.

By Areeb Ahmer, . 5 minute read.

T-SQL has had TRY...CATCH since SQL Server 2005. It looks like the try/catch of C# or Java, and mostly behaves like it, with a few T-SQL-specific rules: some errors never reach your CATCH, a transaction can be left in a state where it can only be rolled back, and the old RAISERROR behaves differently from the newer THROW.

The examples run against the SpDevDemo database.

The basics

BEGIN TRY
    SELECT 1 / 0;
    PRINT 'Not reached';
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER()    AS number,
        ERROR_SEVERITY()  AS severity,
        ERROR_STATE()     AS state,
        ERROR_PROCEDURE() AS procedure_name,
        ERROR_LINE()      AS line,
        ERROR_MESSAGE()   AS message;
END CATCH
number  severity  state  procedure_name  line  message
------  --------  -----  --------------  ----  --------------------------------
8134    16        1      NULL            2     Divide by zero error encountered.

Any error with severity 11 to 19 inside TRY jumps to CATCH. Messages with severity 10 or less (informational, like PRINT and RAISERROR(..., 0, 1)) don’t. Severity 20 and above usually ends the connection.

The ERROR_*() functions work anywhere inside the CATCH block, including in a procedure called from it, and return NULL outside one.

Errors TRY…CATCH can’t catch

Compile errors in the same batch. A syntax error stops the whole batch from compiling, so the TRY never starts. Less obviously, a reference to a table that doesn’t exist is resolved when the statement runs, and that error isn’t caught at the same level either:

BEGIN TRY
    SELECT * FROM dbo.TableThatDoesNotExist;
END TRY
BEGIN CATCH
    PRINT 'Caught it';
END CATCH
Msg 208, Level 16, State 1
Invalid object name 'dbo.TableThatDoesNotExist'.

Caught it is never printed. The same error is caught when it happens one level down, in a procedure or dynamic SQL called from the TRY:

CREATE OR ALTER PROCEDURE dbo.ReadMissingTable
AS
SELECT * FROM dbo.TableThatDoesNotExist;
GO

BEGIN TRY
    EXEC dbo.ReadMissingTable;
END TRY
BEGIN CATCH
    PRINT CONCAT('Caught: ', ERROR_NUMBER(), ' ', ERROR_MESSAGE());
END CATCH
Caught: 208 Invalid object name 'dbo.TableThatDoesNotExist'.

Other cases: attention events (a client timeout or cancel), and KILL, end the batch without running CATCH. That’s one reason to use SET XACT_ABORT ON: it rolls back the open transaction even when your CATCH never runs.

THROW vs RAISERROR

Both raise an error. For new code, use THROW:

THROW RAISERROR
Rethrow the caught error, unchanged THROW; No: you can only raise a new error with the old message
Severity Always 16 Any you choose
Error number Your own, 50000 or above 50000 by default, or a message added with sp_addmessage
Respects SET XACT_ABORT ON Yes No: the batch continues and the transaction stays open
Message formatting Plain string (use FORMATMESSAGE or CONCAT) printf-style: %d, %s
Statement before it Must end with ; No rule 
BEGIN TRY
    THROW 50001, 'The order is already shipped.', 1;
END TRY
BEGIN CATCH
    PRINT CONCAT(ERROR_NUMBER(), ', severity ', ERROR_SEVERITY(), ': ', ERROR_MESSAGE());
END CATCH
50001, severity 16: The order is already shipped.

THROW; with no arguments, inside a CATCH block, rethrows the original error with its original number, message and line, so the caller sees the real problem. That’s what most CATCH blocks should end with.

RAISERROR still has one good use: progress messages with severity 0 and WITH NOWAIT, which appear immediately (see debugging procedures).

Doomed transactions and XACT_STATE()

Inside CATCH, a transaction can be in one of three states, reported by XACT_STATE():

  • 1: a transaction is open and can be committed or rolled back.
  • -1: a transaction is open but doomed: any attempt to write or commit fails, and it can only be rolled back.
  • 0: no transaction.

Whether an error dooms the transaction depends on the error and on XACT_ABORT:

CREATE TABLE dbo.Items (Id INT PRIMARY KEY);
INSERT dbo.Items VALUES (1);
GO

SET XACT_ABORT OFF;
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT dbo.Items VALUES (1);       -- duplicate key
END TRY
BEGIN CATCH
    PRINT CONCAT('XACT_ABORT OFF: XACT_STATE() = ', XACT_STATE());
    IF @@TRANCOUNT > 0 ROLLBACK;
END CATCH
GO

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT dbo.Items VALUES (1);
END TRY
BEGIN CATCH
    PRINT CONCAT('XACT_ABORT ON:  XACT_STATE() = ', XACT_STATE());
    IF @@TRANCOUNT > 0 ROLLBACK;
END CATCH
SET XACT_ABORT OFF;
XACT_ABORT OFF: XACT_STATE() = 1
XACT_ABORT ON:  XACT_STATE() = -1

With XACT_ABORT OFF, the transaction is still committable after the error, and a CATCH block that doesn’t roll back, followed by a COMMIT somewhere, saves half the work. With XACT_ABORT ON, the transaction is doomed and must be rolled back. That’s the safer behavior, and why the template below uses it.

A CATCH block to copy

CREATE OR ALTER PROCEDURE dbo.DoWork
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        -- the work

        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        -- optional: log ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), ERROR_LINE() here,
        -- after the rollback so the log row isn't rolled back with everything else
        THROW;
    END CATCH
END

Three rules it follows:

  1. Roll back first, if there’s anything to roll back.
  2. Log after the rollback, or the log row is undone with the rest. A table variable can carry details across the rollback, since rollbacks don’t affect table variables.
  3. Rethrow. Don’t turn an error into a return code or a SELECT 'Error' that the caller may never check.

Where error handling hides bugs

The most expensive bugs in T-SQL error handling are the silent ones: a CATCH that logs and continues, a procedure that returns 0 after an error, a COMMIT after an error that only ended the statement. When you step through a procedure in SpDev, the debugger stops on errors that nothing catches, on the line that raised them, and shows each variable’s value at that moment, which is usually enough to see why.