Error Handling

22.1 Error Anatomy

Number, Severity (0–25), State, Procedure, Line, Message

Severity 0–10: informational. 11–16: user-correctable. 17–19: resource/software. 20–25: fatal (connection terminated).

22.2 TRY...CATCH

BEGIN TRY
    BEGIN TRAN;
        UPDATE dbo.Accounts SET Balance -= 500 WHERE AccountID = 1;
        UPDATE dbo.Accounts SET Balance += 500 WHERE AccountID = 2;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;

    SELECT ERROR_NUMBER()    AS ErrNum,
           ERROR_SEVERITY()  AS Sev,
           ERROR_STATE()     AS State,
           ERROR_PROCEDURE() AS Proc,
           ERROR_LINE()      AS Line,
           ERROR_MESSAGE()   AS Msg;

    INSERT dbo.ErrorLog (ErrNum, Msg, LoggedAt) VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), SYSDATETIME());
    THROW;   -- re-throw the original error
END CATCH

22.3 THROW vs RAISERROR

Recommended (2012+) Legacy, still useful for WITH NOWAIT and formatting

Re-throws the original error with no args Cannot re-throw the original Preceding statement needs ; Always aborts the batch Does not abort by default

THROW 50001, 'Insufficient stock.', 1;          -- severity is always 16; ends the batch
RAISERROR('Order %d failed for %s', 16, 1, 1001, 'Asha');  -- formatting, any severity
RAISERROR(50005, 16, 1) WITH LOG;               -- user-defined message from sys.messages
EXEC sp_addmessage 50005, 16, N'Custom error: %s';

22.4 XACT_STATE() and XACT_ABORT

Always use SET XACT_ABORT ON in procedures that open transactions. Client timeouts don't trigger CATCH blocks.

-- XACT_STATE(): 1 = committable, -1 = uncommittable (must roll back), 0 = no transaction
BEGIN CATCH
    IF XACT_STATE() = -1 ROLLBACK;
    IF XACT_STATE() = 1  COMMIT;   -- or ROLLBACK depending on logic
END CATCH

22.5 Errors TRY/CATCH Doesn't Catch

Compile errors and deferred name resolution errors in the same scope

Severity ≥ 20 that terminates the connection Attention signals (client timeout or cancel)

22.6 Standard Procedure Template

CREATE OR ALTER PROCEDURE dbo.usp_Template
AS
BEGIN
    SET NOCOUNT, XACT_ABORT ON;
    BEGIN TRY
        BEGIN TRAN;
            -- work
        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;
    END CATCH
END