Error Handling (TRY...CATCH)

Learn BEGIN TRY / BEGIN CATCH in SQL Server, the ERROR_MESSAGE() and ERROR_NUMBER() functions, and how to combine error handling with transactions for safe rollbacks.

Introduction

The previous lesson checked @@ERROR after every single statement to detect failures - functional, but tedious and easy to get wrong. SQL Server's BEGIN TRY / BEGIN CATCH block is a much cleaner way to handle runtime errors: if anything inside the TRY block fails, control jumps immediately to the CATCH block, where you can inspect exactly what went wrong and decide what to do about it, including rolling back a transaction.

What You Will Learn

Why TRY...CATCH is preferred over checking @@ERROR manually.
The basic TRY...CATCH syntax.
How to inspect an error with ERROR_MESSAGE(), ERROR_NUMBER(), and related functions.
How to combine TRY...CATCH with transactions for safe rollback.
The difference between THROW and RAISERROR.

Why TRY...CATCH?

Without TRY...CATCH, an unexpected error (like a constraint violation or a divide-by-zero) can leave a script in an inconsistent state, or silently continue past a failed statement depending on the error's severity. TRY...CATCH gives you a guaranteed, structured way to detect any error in a block of code and respond to it in one place, instead of checking after every individual statement.

Basic TRY...CATCH Syntax

BEGIN TRY
    -- Code that might fail
    SELECT 1 / 0 AS WillFail;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER()  AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Result

Click Run to see what this code prints.

Error Functions

Inside a CATCH block, SQL Server exposes a set of functions that describe the error that was just caught. They only return meaningful values inside CATCH - outside of one, they return NULL.

Function	Returns
ERROR_NUMBER()	The internal SQL Server error number
ERROR_MESSAGE()	The full, human-readable error text
ERROR_SEVERITY()	The severity level of the error
ERROR_LINE()	The line number where the error occurred
ERROR_PROCEDURE()	The name of the procedure/trigger the error happened in, if any

TRY...CATCH with Transactions

The most common real-world pattern is to open a transaction inside a TRY block, and in the matching CATCH block, check whether a transaction is still open (using XACT_STATE()) and roll it back before re-raising or logging the error.

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE dbo.Accounts SET Balance = Balance - 500 WHERE AccountID = 1;
    UPDATE dbo.Accounts SET Balance = Balance + 500 WHERE AccountID = 999; -- invalid account

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

    SELECT
        ERROR_NUMBER()  AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

XACT_STATE()

XACT_STATE() returns 1 for a healthy open transaction, -1 for a transaction that has been doomed and can only be rolled back, and 0 for no open transaction at all. Checking it before ROLLBACK avoids trying to roll back a transaction that no longer exists.

Bank Transfer, Revisited

Here is the bank-transfer example from the previous lesson, rewritten with TRY...CATCH instead of manual @@ERROR checks - considerably cleaner and safer.

CREATE PROCEDURE dbo.TransferFunds
    @FromAccountID INT,
    @ToAccountID   INT,
    @Amount        DECIMAL(12,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE dbo.Accounts SET Balance = Balance - @Amount WHERE AccountID = @FromAccountID;
        UPDATE dbo.Accounts SET Balance = Balance + @Amount WHERE AccountID = @ToAccountID;

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

        THROW;
    END CATCH
END;
GO

EXEC dbo.TransferFunds @FromAccountID = 1, @ToAccountID = 2, @Amount = 250;

THROW and RAISERROR

THROW, with no arguments, re-raises the error currently being handled in a CATCH block, preserving its original number, message, and severity - as used above. RAISERROR is the older syntax and is still useful for raising custom, formatted error messages, but THROW (available since SQL Server 2012) is generally preferred for its simpler, more consistent behavior.

-- Raising a custom error directly
IF NOT EXISTS (SELECT 1 FROM dbo.Accounts WHERE AccountID = @FromAccountID)
    THROW 51000, 'Source account does not exist.', 1;

Common Mistakes

Avoid These Mistakes

Forgetting to check XACT_STATE() before ROLLBACK, which can itself raise an error if no transaction is open.
Swallowing errors silently in CATCH without logging or re-throwing them.
Not setting SET XACT_ABORT ON, which means some errors won't automatically abort the transaction the way you might expect.
Using RAISERROR with severity levels above 18 without special permissions, which will itself fail.
Calling ERROR_MESSAGE() outside of a CATCH block and expecting it to return the last error - it only works inside CATCH.

Best Practices

Pair every open transaction with a TRY...CATCH that guarantees a ROLLBACK on failure.
Use SET XACT_ABORT ON in procedures that manage transactions.
Log ERROR_NUMBER(), ERROR_MESSAGE(), and ERROR_LINE() to an error table for diagnosis in production.
Use THROW to preserve the original error's details when re-raising.
Keep TRY blocks focused - do not wrap unrelated logic together just to share one CATCH block.

Frequently Asked Questions

Does TRY...CATCH catch every kind of error?

It catches most runtime errors, but not errors with a severity of 20 or higher, which terminate the connection, and it does not catch compile-time or parse errors in the batch itself.

What is the difference between THROW and RAISERROR?

THROW re-raises the current error with its original details and requires no formatting; RAISERROR lets you build custom, formatted messages but has more inconsistent behavior around severity and requires a semicolon-safe syntax.

Can I nest TRY...CATCH blocks?

Yes, a CATCH block can contain its own TRY...CATCH, which is useful when you want to attempt a recovery action and still handle a failure of that recovery.

Should every stored procedure use TRY...CATCH?

Any procedure that performs writes, especially across multiple statements or tables, should generally use it. Simple read-only SELECT procedures often do not need it.

Key Takeaways

BEGIN TRY / BEGIN CATCH structures error handling far more cleanly than manual @@ERROR checks.
ERROR_MESSAGE(), ERROR_NUMBER(), and related functions only return data inside a CATCH block.
Check XACT_STATE() before rolling back inside a CATCH block.
SET XACT_ABORT ON makes most runtime errors abort the transaction automatically.
THROW re-raises the original error and is generally preferred over RAISERROR.

Summary

TRY...CATCH, combined with transactions, is the standard, reliable way to write T-SQL that fails safely instead of leaving data half-changed. Next, you will look at window functions, a powerful tool for calculations like running totals and rankings that span across rows.

Next Lesson →