Exception Handling using TRY/CATCH in SQL Server

T-SQL has structured error handling: wrap risky statements in BEGIN TRY … END TRY, and when anything fails, control jumps to BEGIN CATCH … END CATCH where you can inspect the error, roll back cleanly, and re-raise. This tutorial demonstrates the full pattern against real constraint violations (outputs are actual), including the transaction-safety template every stored procedure should use.

The basic shape

BEGIN TRY
    INSERT INTO Products (Sku, Name, Price) VALUES ('BK-003', 'Bad Bike', -5);
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER()   AS ErrorNumber,
           ERROR_SEVERITY() AS Severity,
           ERROR_LINE()     AS Line,
           ERROR_MESSAGE()  AS Message;
END CATCH;

ErrorNumber  Severity  Line  Message
-----------  --------  ----  ------------------------------------------------
547          16        3     The INSERT statement conflicted with the CHECK
                             constraint "CK_Products_Price"...

The negative price violated a CHECK constraint; instead of the batch aborting with a red error, the CATCH block ran and the error functions reported everything about it.

The six error functions — valid only inside CATCH:

Call them twice, get the same answer — they're stable throughout the CATCH block.

The transaction-safety template

The pattern that matters most: a multi-statement change where failure must undo everything. Deduct from one order, insert another — the insert hits a foreign-key violation:

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Orders SET Amount = Amount - 100 WHERE OrderId = 1;

    INSERT INTO Orders (CustomerId, OrderDate, Amount)
    VALUES (999, '2026-07-20', 100);          -- customer 999 doesn't exist → FK error

    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;

    SELECT 'rolled back, order 1 amount untouched:' AS Note, Amount
    FROM Orders WHERE OrderId = 1;
END CATCH;

Note                                     Amount
---------------------------------------  -------
rolled back, order 1 amount untouched:   1200.00

The UPDATE had already run — and the ROLLBACK undid it. Without TRY/CATCH, the update would have committed while the insert failed: a half-applied business operation, the worst kind of bug. The IF @@TRANCOUNT > 0 guard makes the CATCH safe even when the error occurred before the transaction started.

For strictness, add SET XACT_ABORT ON at the top of procedures: it makes almost every error doom the transaction outright, closing edge cases where a transaction limps on in an uncommittable state (check XACT_STATE() if you need to distinguish: -1 = must roll back).

Re-raising: THROW

Handling an error locally is only half the job — callers usually need to know. Re-raise the original error:

BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK;
    THROW;                     -- re-raises the original error, number and all
END CATCH;

Or raise your own, with a number ≥ 50000:

IF NOT EXISTS (SELECT 1 FROM Customers WHERE CustomerId = @CustomerId)
    THROW 50001, 'Customer does not exist.', 1;

THROW (introduced 2012) supersedes the older RAISERROR for almost every use — it preserves the original error number on bare re-throw and always terminates the batch. RAISERROR remains for printf-style formatting and WITH NOWAIT progress messages.

The production procedure skeleton

Putting it together — the shape worth copying into every writing procedure (compare the plain version in stored procedure vs function):

CREATE OR ALTER PROCEDURE dbo.TransferOrderAmount
    @FromOrderId INT, @ToOrderId INT, @Amount DECIMAL(10,2)
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE Orders SET Amount = Amount - @Amount WHERE OrderId = @FromOrderId;
        UPDATE Orders SET Amount = Amount + @Amount WHERE OrderId = @ToOrderId;

        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;

        INSERT INTO dbo.ErrorLog (ErrorNumber, ErrorMessage, ErrorProcedure, LoggedAt)
        VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), SYSUTCDATETIME());

        THROW;    -- caller (app / EF Core / job) sees the real failure
    END CATCH;
END;

Note the ErrorLog insert happens after the rollback — inside the doomed transaction it would be rolled back too. (A table variable filled before the rollback is the other way to preserve diagnostic rows, since table variables survive rollback.)

What TRY/CATCH doesn't catch

• Compile errors in the same scope — a typo'd table name aborts before TRY runs. (It does catch them when they occur one level deeper, e.g. inside dynamic SQL or a called procedure.)
• Severity ≥ 20 (connection-killing) and KILL/timeout aborts — the client sees those.
• Warnings and PRINT output — not errors at all.

Client timeouts deserve a mention: when ADO.NET cancels a long-running call, your CATCH may never run — one more reason SET XACT_ABORT ON belongs in the template, so the aborted transaction rolls back on its own.