T-SQL Error Handling

Master T-SQL error handling — TRY/CATCH blocks, the ERROR_*() functions, severity levels, RAISERROR vs THROW (with the THROW recommendation for modern code), nested TRY/CATCH, the XACT_ABORT and XACT_STATE() patterns for transactions, error logging procedures, and what TRY/CATCH cannot catch.

Error handling in T-SQL is the practice of catching errors at runtime — division by zero, primary-key violations, missing tables, deadlocks — and responding to them in a controlled way: rolling back transactions, logging details, raising friendlier messages to the caller. Without it, an unhandled error stops execution mid-batch and can leave your data half-modified.

Modern T-SQL (SQL Server 2005+) provides a structured exception model based on TRY ... CATCH blocks, plus a set of ERROR_*() functions that expose details about what went wrong. SQL Server 2012 added the THROW statement which made re-throwing far cleaner than the legacy RAISERROR.

Every error has a number, severity, state, and message. Numbers below 50000 are reserved by SQL Server; 50000 and above are available for user-defined errors.

Severity controls whether an error even reaches your CATCH block:

The structured way to catch and respond to errors. Code that might fail goes inside BEGIN TRY ... END TRY; the response goes inside the matching BEGIN CATCH ... END CATCH:

The ERROR_*() Functions

These functions return details about the error currently being handled. They only return values inside a CATCH block — outside CATCH, every one of them returns NULL.

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

The TRY block raised error 8134; control jumped to CATCH where the ERROR_*() functions filled in the details. Without TRY/CATCH, the same query would have terminated the batch with a red error message in SSMS.

Before SQL Server 2005, error handling meant checking @@ERROR after every statement. The function returns the error number of the last executed statement — but resets immediately on the next statement, including successful ones.

CREATE TABLE TestTable (ID INT PRIMARY KEY);
INSERT INTO TestTable (ID) VALUES (1);

DECLARE @ErrorNumber INT;

-- This will fail — duplicate primary key
INSERT INTO TestTable (ID) VALUES (1);

-- Capture @@ERROR IMMEDIATELY — the next statement resets it
SET @ErrorNumber = @@ERROR;

IF @ErrorNumber <> 0
BEGIN
    PRINT 'An error occurred. Error number: ' +
          CAST(@ErrorNumber AS NVARCHAR(10));
END

Note the spelling: RAISERROR (one E in the middle, not two). It's a quirk of T-SQL history.

RAISERROR ('A custom error occurred', 16, 1);

RAISERROR also supports formatted messages with printf-style placeholders:

DECLARE @account NVARCHAR(20) = 'AC-1042', @balance MONEY = -50;

RAISERROR ('Account %s has negative balance %d', 16, 1, @account, @balance);

Introduced in SQL Server 2012, THROW is the recommended replacement for RAISERROR — simpler syntax, doesn't lose the original error context when re-throwing.

THROW 50000, 'A custom error message', 1;

The killer feature of THROW — re-throwing inside a CATCH block preserves the original error number, message, and line. This is far better than the old RAISERROR(@msg, ...) pattern, which loses the original error number.

BEGIN TRY
    INSERT INTO TestTable (ID) VALUES (1);    -- duplicate key
END TRY
BEGIN CATCH
    -- log the error somewhere first
    PRINT 'Error caught: ' + ERROR_MESSAGE();

    -- re-throw with full fidelity — original error 2627 is preserved
    THROW;
END CATCH;

The canonical structure for any procedure that does multi-statement DML and needs to be safe under failure.

CREATE TABLE SampleTable (
    ID    INT PRIMARY KEY,
    Name  NVARCHAR(50) NOT NULL,
    Age   INT CHECK (Age > 0)
);

CREATE TABLE ErrorLog (
    ErrorID         INT IDENTITY(1,1) PRIMARY KEY,
    ErrorNumber     INT,
    ErrorMessage    NVARCHAR(4000),
    ErrorSeverity   INT,
    ErrorState      INT,
    ErrorLine       INT,
    ErrorProcedure  NVARCHAR(200),
    ErrorDateTime   DATETIME2 DEFAULT SYSDATETIME()
);

CREATE OR ALTER PROCEDURE InsertData
    @ID    INT,
    @Name  NVARCHAR(50),
    @Age   INT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;     -- ensures any error rolls back the transaction

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT INTO SampleTable (ID, Name, Age)
        VALUES (@ID, @Name, @Age);

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        -- Roll back if a transaction is still active
        IF XACT_STATE() <> 0
            ROLLBACK TRANSACTION;

        -- Log the error
        INSERT INTO ErrorLog (ErrorNumber, ErrorMessage, ErrorSeverity,
                              ErrorState, ErrorLine, ErrorProcedure)
        VALUES (ERROR_NUMBER(),    ERROR_MESSAGE(),  ERROR_SEVERITY(),
                ERROR_STATE(),     ERROR_LINE(),     ERROR_PROCEDURE());

        -- Re-throw so the caller knows something went wrong
        THROW;
    END CATCH
END;
GO

Test it with a valid call and an invalid one:

EXEC InsertData @ID = 1, @Name = 'John Doe', @Age = 30;

EXEC InsertData @ID = 2, @Name = 'Alice', @Age = -5;

And inspect the log:

SELECT ErrorID, ErrorNumber, ErrorSeverity, ErrorLine, ErrorProcedure, ErrorMessage
FROM   ErrorLog
ORDER BY ErrorID;

You can nest TRY/CATCH blocks to handle errors at multiple levels. The inner CATCH gets first crack; if it doesn't re-throw, the outer code continues normally. If it does THROW, control jumps to the outer CATCH.

CREATE TABLE ParentTable (
    ParentID    INT IDENTITY(1,1) PRIMARY KEY,
    ParentData  NVARCHAR(50)
);

CREATE TABLE ChildTable (
    ChildID    INT IDENTITY(1,1) PRIMARY KEY,
    ParentID   INT,
    ChildData  NVARCHAR(50) NOT NULL,
    CONSTRAINT FK_Parent FOREIGN KEY (ParentID) REFERENCES ParentTable(ParentID)
);

CREATE OR ALTER PROCEDURE InsertParentAndChild
    @ParentData  NVARCHAR(50),
    @ChildData   NVARCHAR(50)
AS
BEGIN
    SET NOCOUNT ON;

    BEGIN TRY
        INSERT INTO ParentTable (ParentData) VALUES (@ParentData);

        DECLARE @ParentID INT = SCOPE_IDENTITY();

        -- Inner TRY/CATCH — keep parent insert even if child fails
        BEGIN TRY
            INSERT INTO ChildTable (ParentID, ChildData)
            VALUES (@ParentID, @ChildData);
        END TRY
        BEGIN CATCH
            -- Log child failure but don't re-throw — parent stays
            INSERT INTO ErrorLog (ErrorNumber, ErrorMessage)
            VALUES (ERROR_NUMBER(), ERROR_MESSAGE());
        END CATCH;

    END TRY
    BEGIN CATCH
        -- Outer CATCH — parent insert itself failed
        INSERT INTO ErrorLog (ErrorNumber, ErrorMessage)
        VALUES (ERROR_NUMBER(), ERROR_MESSAGE());

        THROW;        -- bubble up to caller
    END CATCH;
END;
GO

-- Valid call
EXEC InsertParentAndChild 'Sankar', 'Dhileep Kumar';

-- Child fails (NOT NULL violation), but parent still inserts
EXEC InsertParentAndChild 'Sankar', NULL;

SELECT * FROM ParentTable;

Both calls created a parent row. The second call's child insert failed with a NOT NULL violation, the inner CATCH logged it, but the parent stayed because the inner CATCH didn't re-throw.

TRY/CATCH is powerful but not universal. These errors bypass it entirely:

1. Use TRY/CATCH for any non-trivial T-SQL. The legacy @@ERROR pattern is fragile — replace it.
2. Use THROW, not RAISERROR, for new code — especially the bare THROW; in CATCH blocks for clean re-throws.
3. Always SET XACT_ABORT ON in procedures that modify data — it makes some otherwise-trappable errors automatically roll back the transaction.
4. Use XACT_STATE() in CATCH blocks to test transaction state before COMMIT or ROLLBACK.
5. Log errors before re-throwing. The caller may not log; persist what you know in your own table.
6. Don't swallow errors silently. An empty CATCH block is almost always a bug — at minimum, log and re-throw.
7. Pick a sensible severity. Use 16 for normal user-correctable errors; 11 is the minimum that reaches CATCH; below that is informational only.
8. Always end the statement before THROW with a semicolon. The single most common THROW bug.

• T-SQL has four error-handling tools: TRY ... CATCH (modern, structured), @@ERROR (legacy), RAISERROR (legacy raise/format), and THROW (modern raise/re-throw).
• Severity 11+ enters CATCH; 0–10 are informational; 20+ kill the connection and bypass the local CATCH.
• ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE() populate inside CATCH — all return NULL outside.
• Note the spelling: RAISERROR (one E in the middle), not RAISEERROR.
• THROW > RAISERROR for new code: simpler, preserves error context on re-throw via the bare THROW; form.
• For data-modifying procedures, the canonical pattern is: SET XACT_ABORT ON, BEGIN TRY ... BEGIN TRANSACTION ... COMMIT ... END TRY, BEGIN CATCH with IF XACT_STATE() <> 0 ROLLBACK, log, then THROW.