TRY CATCH Error Handling in SQL Server (Advanced)

BEGIN TRY
    -- statements that may fail
END TRY
BEGIN CATCH
    -- runs only if an error (severity 11-19) happens in TRY
    ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(),
    ERROR_STATE(), ERROR_LINE(), ERROR_PROCEDURE()
END CATCH

NOT caught by TRY...CATCH:

• Compile / syntax errors in the same batch
• "Object not found" errors in the same scope (deferred name resolution)
• Severity 0-10 (just warnings / info messages)
• Severity 20+ that kill the connection

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic tables: ErrorLog and a Payments table with constraints

DROP TABLE IF EXISTS dbo.ErrorLog;
DROP TABLE IF EXISTS dbo.Payments;

CREATE TABLE dbo.ErrorLog
(
    ErrorLogID     INT IDENTITY(1,1) PRIMARY KEY,
    ErrorNumber    INT,
    ErrorSeverity  INT,
    ErrorState     INT,
    ErrorProcedure NVARCHAR(128),
    ErrorLine      INT,
    ErrorMessage   NVARCHAR(4000),
    UserName       SYSNAME      DEFAULT SUSER_SNAME(),
    LoggedAt       DATETIME2(0) DEFAULT SYSDATETIME()
);

CREATE TABLE dbo.Payments
(
    PaymentID   INT IDENTITY(1,1) PRIMARY KEY,
    OrderID     INT           NOT NULL REFERENCES dbo.Orders(OrderID),
    Amount      DECIMAL(12,2) NOT NULL CHECK (Amount > 0),
    PaymentMode VARCHAR(10)   NOT NULL CHECK (PaymentMode IN ('UPI', 'Card', 'Cash', 'NetBank')),
    PaidOn      DATE          NOT NULL DEFAULT CAST(GETDATE() AS DATE),
    ReferenceNo VARCHAR(20)   NULL
);

-- A UNIQUE constraint allows only ONE NULL in SQL Server.
-- A filtered unique index allows many NULLs but keeps real values unique (see Indexes/28).
CREATE UNIQUE INDEX UX_Payments_ReferenceNo ON dbo.Payments (ReferenceNo) WHERE ReferenceNo IS NOT NULL;

INSERT INTO dbo.Payments (OrderID, Amount, PaymentMode, PaidOn, ReferenceNo) VALUES
(1, 65000, 'UPI',  '2024-01-10', 'UPI0001'),
(4, 12000, 'Card', '2024-01-30', 'CRD0001'),
(5,  2500, 'Cash', '2024-02-05', NULL);
GO

-- Reusable procedure that logs the current error (call it inside CATCH blocks)
CREATE OR ALTER PROCEDURE dbo.usp_LogError
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.ErrorLog (ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorMessage)
    VALUES (ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE());
END;
GO

(3 rows affected)

Part A: Basic TRY...CATCH and Error Functions

Divide by zero

BEGIN TRY
    SELECT 100 / 0 AS Result;
    PRINT 'This line is skipped';
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER()    AS ErrorNumber,
           ERROR_SEVERITY()  AS Severity,
           ERROR_STATE()     AS State,
           ERROR_LINE()      AS Line,
           ERROR_MESSAGE()   AS Message;
END CATCH;
GO

Common errors you will meet (each is caught and logged)

BEGIN TRY  INSERT INTO dbo.Payments (OrderID, Amount, PaymentMode) VALUES (1, -50, 'UPI');          END TRY  -- CHECK constraint (547)
BEGIN CATCH EXEC dbo.usp_LogError; END CATCH;

BEGIN TRY  INSERT INTO dbo.Payments (OrderID, Amount, PaymentMode) VALUES (9999, 500, 'UPI');       END TRY  -- FOREIGN KEY (547)
BEGIN CATCH EXEC dbo.usp_LogError; END CATCH;

BEGIN TRY  INSERT INTO dbo.Payments (OrderID, Amount, PaymentMode, ReferenceNo) VALUES (2, 10, 'UPI', 'UPI0001'); END TRY  -- UNIQUE index (2601)
BEGIN CATCH EXEC dbo.usp_LogError; END CATCH;

BEGIN TRY  INSERT INTO dbo.Payments (OrderID, Amount, PaymentMode) VALUES (2, 10, NULL);            END TRY  -- NOT NULL (515)
BEGIN CATCH EXEC dbo.usp_LogError; END CATCH;

BEGIN TRY  SELECT CAST('abc' AS INT);                                                              END TRY  -- conversion (245)
BEGIN CATCH EXEC dbo.usp_LogError; END CATCH;

BEGIN TRY  DECLARE @t TINYINT = 300;                                                               END TRY  -- overflow (220)
BEGIN CATCH EXEC dbo.usp_LogError; END CATCH;

SELECT ErrorNumber, ErrorSeverity, ErrorLine, ErrorMessage FROM dbo.ErrorLog ORDER BY ErrorLogID;
GO

(0 rows affected)
(0 rows affected)
(0 rows affected)
(0 rows affected)

Part B: TRY...CATCH with Transactions (the standard template)

Without TRY...CATCH: the first INSERT stays even though the 2nd fails (no atomicity)

With TRY...CATCH + transaction: all or nothing

DECLARE @Before INT = (SELECT COUNT(*) FROM dbo.Payments);

BEGIN TRY
    BEGIN TRAN;
        INSERT INTO dbo.Payments (OrderID, Amount, PaymentMode, ReferenceNo) VALUES (6, 1000, 'UPI', 'UPI0100');   -- OK
        INSERT INTO dbo.Payments (OrderID, Amount, PaymentMode, ReferenceNo) VALUES (7, 2000, 'Bitcoin', 'X1');    -- fails CHECK
    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRAN;           -- undo the first insert too
    EXEC dbo.usp_LogError;
    PRINT 'Transaction rolled back: ' + ERROR_MESSAGE();
END CATCH;

SELECT @Before AS RowsBefore, COUNT(*) AS RowsAfter FROM dbo.Payments;   -- same number
GO

(1 row affected)
(0 rows affected)
Transaction rolled back: The INSERT statement conflicted with the CHECK constraint "CK__Payments__Paymen__0B47A151". The conflict occurred in database "SqlLearningDb", table "dbo.Payments", column 'PaymentMode'.

Part C: XACT_STATE() - is the transaction still usable?

1 = active and committable 0 = no transaction -1 = "doomed" transaction : you can ONLY roll back

SET XACT_ABORT ON;   -- with this ON, most errors make the transaction doomed (-1)
BEGIN TRY
    BEGIN TRAN;
        INSERT INTO dbo.Payments (OrderID, Amount, PaymentMode) VALUES (8, 0, 'UPI');   -- fails CHECK
    COMMIT TRAN;
END TRY
BEGIN CATCH
    SELECT XACT_STATE() AS XactState, @@TRANCOUNT AS TranCount, ERROR_MESSAGE() AS Msg;

    IF XACT_STATE() = -1 ROLLBACK TRAN;      -- doomed -> must roll back
    IF XACT_STATE() =  1 COMMIT TRAN;        -- still OK -> may commit partial work (rarely wanted)
END CATCH;
SET XACT_ABORT OFF;
GO

(0 rows affected)

Part D: TRY...CATCH Inside a Stored Procedure (production template)

CREATE OR ALTER PROCEDURE dbo.usp_RecordPayment
    @OrderID     INT,
    @Amount      DECIMAL(12,2),
    @PaymentMode VARCHAR(10),
    @ReferenceNo VARCHAR(20) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        -- business rule check
        IF NOT EXISTS (SELECT 1 FROM dbo.Orders WHERE OrderID = @OrderID AND Status <> 'Cancelled')
            THROW 50010, 'Order not found or cancelled.', 1;

        BEGIN TRAN;
            INSERT INTO dbo.Payments (OrderID, Amount, PaymentMode, ReferenceNo)
            VALUES (@OrderID, @Amount, @PaymentMode, @ReferenceNo);

            UPDATE dbo.Orders SET Status = 'Shipped', ShipDate = CAST(GETDATE() AS DATE)
            WHERE OrderID = @OrderID AND Status = 'Pending';
        COMMIT TRAN;

        SELECT 'SUCCESS' AS Result, SCOPE_IDENTITY() AS PaymentID;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRAN;
        EXEC dbo.usp_LogError;          -- log first ...
        THROW;                          -- ... then re-raise to the caller (keeps original error number)
    END CATCH;
END;
GO

-- Success case (inside outer transaction we roll back, to keep data clean)
BEGIN TRAN;
    EXEC dbo.usp_RecordPayment @OrderID = 10, @Amount = 5000, @PaymentMode = 'UPI', @ReferenceNo = 'UPI0200';
ROLLBACK TRAN;
GO

-- Failure case : caller also uses TRY...CATCH
BEGIN TRY
    EXEC dbo.usp_RecordPayment @OrderID = 2, @Amount = 5000, @PaymentMode = 'UPI';   -- order 2 is cancelled
END TRY
BEGIN CATCH
    SELECT 'Caller caught' AS Info, ERROR_NUMBER() AS ErrNo, ERROR_MESSAGE() AS ErrMsg, ERROR_PROCEDURE() AS InProc;
END CATCH;
GO

Part E: Nested TRY...CATCH and Retry Logic

Nested: inner error handled locally, outer code continues

BEGIN TRY
    PRINT 'Outer: start';
    BEGIN TRY
        SELECT 1 / 0;
    END TRY
    BEGIN CATCH
        PRINT 'Inner catch handled: ' + ERROR_MESSAGE();
    END CATCH;
    PRINT 'Outer: continues after inner error';
END TRY
BEGIN CATCH
    PRINT 'Outer catch (not reached)';
END CATCH;
GO

Outer: start

Inner catch handled: Divide by zero error encountered.
Outer: continues after inner error

Retry pattern (used for deadlocks 1205 / timeouts). Here we simulate failures.

DECLARE @Attempt INT = 1, @MaxAttempts INT = 3, @Done BIT = 0;

WHILE @Attempt <= @MaxAttempts AND @Done = 0
BEGIN
    BEGIN TRY
        PRINT CONCAT('Attempt ', @Attempt);
        IF @Attempt < 3                       -- pretend the first two attempts hit a deadlock
            THROW 51205, 'Simulated deadlock', 1;

        PRINT 'Work completed successfully';
        SET @Done = 1;
    END TRY
    BEGIN CATCH
        IF ERROR_NUMBER() IN (1205, 51205) AND @Attempt < @MaxAttempts
        BEGIN
            PRINT 'Retryable error, waiting before retry...';
            WAITFOR DELAY '00:00:01';
            SET @Attempt += 1;
        END
        ELSE
        BEGIN
            PRINT 'Giving up';
            THROW;
        END
    END CATCH;
END;
GO

Attempt 1
Retryable error, waiting before retry...
Attempt 2
Retryable error, waiting before retry...
Attempt 3
Work completed successfully

Part F: Errors That TRY...CATCH Cannot CATCH in the Same Scope

Missing table in the same batch -> NOT caught (uncomment to see)

BEGIN TRY  SELECT * FROM dbo.TableThatDoesNotExist;  END TRY
BEGIN CATCH PRINT 'You will NOT see this'; END CATCH;

Same error inside dynamic SQL (a lower scope) -> IS caught

BEGIN TRY
    EXEC (N'SELECT * FROM dbo.TableThatDoesNotExist;');
END TRY
BEGIN CATCH
    SELECT 'Caught from lower scope' AS Info, ERROR_NUMBER() AS ErrNo, ERROR_MESSAGE() AS ErrMsg;
END CATCH;
GO

Practice Questions

1. Write a TRY...CATCH that inserts 3 payments in a transaction; make the 3rd fail; verify nothing was saved.
2. Change usp_LogError to also save the XACT_STATE() value.
3. Write a procedure that transfers stock between two products and rolls back if the source goes negative.
4. Find the most frequent ErrorNumber in ErrorLog.