Exception Handling using TRY/CATCH in SQL Server

Exception handling in SQL Server is done with a TRY...CATCH block: you wrap risky statements in BEGIN TRY ... END TRY, and when any of them raises a runtime error, control jumps to the matching BEGIN CATCH ... END CATCH block instead of aborting the batch. Inside CATCH you inspect the error with functions like ERROR_MESSAGE(), decide whether to roll back a transaction, and re-raise the error to the caller with THROW. This article shows the complete pattern on SQL Server 2022 T-SQL: the construct, the error functions, transaction rollback, THROW versus RAISERROR, custom errors, SET XACT_ABORT ON, and what TRY...CATCH cannot catch.

How does TRY...CATCH work?

A TRY...CATCH block has two parts. The TRY block holds the statements that might fail. If every statement succeeds, the CATCH block is skipped entirely. If any statement produces an error with a severity of 11 or higher, execution stops at that statement and jumps straight to the CATCH block — no further TRY statements run.

BEGIN TRY
    -- statements that might fail
    DECLARE @result INT = 10 / 0;   -- divide by zero
    PRINT 'This line never runs';
END TRY
BEGIN CATCH
    PRINT 'Something went wrong: ' + ERROR_MESSAGE();
END CATCH;

The divide-by-zero error transfers control to CATCH, so the PRINT inside TRY is skipped and you see the error message instead. The batch itself does not terminate — the code after END CATCH continues to run. This is the key behavioral change over unhandled errors, which abort the batch and surface a raw message to the client. You can also nest TRY...CATCH blocks: put another TRY...CATCH inside a CATCH block to handle a failure that happens while you are cleaning up, such as an error writing to a log table.

Which error functions can you call inside CATCH?

The error functions are only meaningful inside a CATCH block; called anywhere else they return NULL. They describe the error that triggered the block:

ERROR_NUMBER() — the error number (e.g. 8134 for divide by zero).
ERROR_MESSAGE() — the full message text, with parameters substituted.
ERROR_SEVERITY() — the severity level (11–19 for catchable errors).
ERROR_STATE() — the state, useful for locating which raise site fired.
ERROR_LINE() — the line number where the error occurred.
ERROR_PROCEDURE() — the stored procedure or trigger name, or NULL for ad-hoc batches.

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;

Capture these into variables if you want to log the failure to an audit table before you re-raise it, since the functions stop returning the error details once control leaves the CATCH block.

Combine TRY...CATCH with transactions

The real value of error handling appears when several statements must succeed or fail as a unit. Wrap them in a transaction, commit at the end of the TRY block, and roll back in CATCH. Before rolling back, check XACT_STATE(), which tells you the transaction's condition:

1 — an active, committable transaction; you can commit or roll back.
-1 — an active but uncommittable (doomed) transaction; you must roll back.
0 — no open transaction; there is nothing to roll back.

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE dbo.Account SET Balance = Balance - 100 WHERE Id = 1;
    UPDATE dbo.Account SET Balance = Balance + 100 WHERE Id = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    THROW;   -- re-raise to the caller
END CATCH;

SQL TRY CATCH block with a transaction and THROW

Checking XACT_STATE() instead of blindly calling ROLLBACK avoids the "no corresponding BEGIN TRANSACTION" error that occurs when the transaction was already rolled back automatically.

Re-raise errors with THROW

When you catch an error, log it, and want the caller to know it failed, re-raise it. Since SQL Server 2012 the preferred statement is THROW. With no arguments inside a CATCH block, it re-raises the original error unchanged, preserving the error number and, crucially, the original line number.

BEGIN CATCH
    -- log here if needed, then bubble the error up
    THROW;
END CATCH;

THROW also raises new errors. The syntax is THROW error_number, message, state, where the number must be 50000 or greater:

THROW 50001, 'Customer credit limit exceeded.', 1;

THROW vs RAISERROR: which should you use?

Both raise errors, but they behave differently. Prefer THROW for new code; reach for RAISERROR only when you need its formatting or lower severity levels.

THROW always uses severity 16, stops execution immediately, and reports the correct line number. It does not do printf-style substitution.
RAISERROR supports parameterized messages with %s and %d, lets you pick a severity, and can raise informational messages (severity 10 or below) without transferring control to CATCH.
THROW does not require a message to exist in sys.messages; RAISERROR with a custom number over 50000 does unless you use an ad-hoc string.

-- RAISERROR with formatting
RAISERROR('Order %d for customer %s failed.', 16, 1, @OrderId, @Customer);

Define custom errors in the 50000 range

Application-specific errors use numbers of 50000 and above. For a one-off message, pass the text directly to THROW. For a reusable message, register it once with sp_addmessage and raise it by number:

EXEC sys.sp_addmessage
    @msgnum   = 50010,
    @severity = 16,
    @msgtext  = 'Insufficient stock for product %d.',
    @lang     = 'us_english';

-- later, from any procedure
RAISERROR(50010, 16, 1, @ProductId);

Turn on SET XACT_ABORT ON

SET XACT_ABORT ON tells SQL Server to automatically abort the batch and roll back the transaction when a runtime error occurs. Inside a TRY...CATCH, the error still routes to CATCH, but the transaction is guaranteed to be uncommittable, which keeps your rollback logic consistent. It is strongly recommended in stored procedures that run transactions, and it also handles edge cases (like query timeouts) that TRY...CATCH alone misses. Put it at the top of the procedure.

What does TRY...CATCH NOT catch?

TRY...CATCH is not a universal safety net. It does not catch:

Compile errors such as syntax mistakes or an invalid object name in the same batch — the batch never starts, so CATCH never runs.
Statement-level recompilation errors, like a mistyped column name resolved at execution time in the batch that contains the TRY.
Severity 20 and above — these terminate the connection, so there is nothing to catch.
Severity 10 and below — informational messages are not errors and do not fire CATCH.

To handle a compile-time or deferred-name error, move the risky statement into a separate procedure or EXEC (@sql) dynamic call, then wrap that call in TRY...CATCH.

A stored-procedure template with proper error handling

Combine everything into a reusable pattern. This is the shape most production procedures on SQL Server 2022 should follow:

CREATE OR ALTER PROCEDURE dbo.TransferFunds
    @FromId INT,
    @ToId   INT,
    @Amount DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        IF @Amount <= 0
            THROW 50001, 'Amount must be positive.', 1;

        BEGIN TRANSACTION;

        UPDATE dbo.Account SET Balance = Balance - @Amount WHERE Id = @FromId;
        UPDATE dbo.Account SET Balance = Balance + @Amount WHERE Id = @ToId;

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        -- optional: INSERT into an error log table here
        THROW;
    END CATCH;
END;

Key takeaways

Wrap risky statements in BEGIN TRY; handle failures in BEGIN CATCH. Errors of severity 11–19 transfer control instead of aborting the batch.
The ERROR_* functions (ERROR_NUMBER, ERROR_MESSAGE, ERROR_SEVERITY, ERROR_STATE, ERROR_LINE, ERROR_PROCEDURE) work only inside CATCH.
With transactions, check XACT_STATE() before rolling back, and commit at the end of the TRY block.
Prefer THROW to re-raise errors — it preserves the original number and line; use RAISERROR when you need formatting or custom severity.
Enable SET XACT_ABORT ON in procedures that run transactions for consistent rollback behavior.
TRY...CATCH does not catch compile errors, deferred-name resolution errors, or severity 20+ errors.

Frequently asked questions

What is the difference between THROW and RAISERROR in SQL Server?

THROW (SQL Server 2012 and later) re-raises the original error with its number and line intact and always uses severity 16. RAISERROR is older but supports printf-style message formatting, custom severity levels, and informational messages that do not fire CATCH. Use THROW for new code and RAISERROR when you need its formatting.

Does TRY...CATCH roll back a transaction automatically?

No. TRY...CATCH transfers control to the CATCH block, but you must roll back the transaction yourself. Check XACT_STATE() and call ROLLBACK TRANSACTION when it returns a non-zero value. Adding SET XACT_ABORT ON makes the transaction uncommittable on error so your rollback is reliable.

Why is my error not being caught by TRY...CATCH?

The most common reasons are compile-time errors (syntax or invalid object names in the same batch), which prevent the batch from running, and severity 20+ errors, which close the connection. Move deferred-name statements into a separate procedure or dynamic SQL so the error surfaces at runtime where CATCH can handle it.

Which error number range should custom errors use?

Use 50000 and above for application-defined errors. Numbers below 50000 are reserved for SQL Server's built-in messages. Pass the text directly with THROW 50001, '...', 1, or register a reusable message with sp_addmessage and raise it by number.