Transact‑SQL Error Handling

Error Handling in Transact‑SQL

Overview

Transact‑SQL provides structured error handling using TRY…CATCH blocks, the THROW statement, and the legacy RAISEERROR. These mechanisms let you catch runtime errors, return custom error messages, and control transaction flow.

TRY…CATCH

Wrap the code that might generate an error in a TRY block. If an error occurs, control is transferred to the associated CATCH block.

BEGIN TRY
    -- Potentially error‑prone statements
    INSERT INTO dbo.Products (Name, Price) VALUES ('Widget', -5);
END TRY
BEGIN CATCH
    SELECT 
        ERROR_NUMBER()   AS ErrorNumber,
        ERROR_SEVERITY() AS Severity,
        ERROR_STATE()    AS State,
        ERROR_MESSAGE()  AS Message;
END CATCH;

THROW

Use THROW to raise an exception and optionally re‑throw the original error. It is the preferred method over RAISEERROR for new development.

BEGIN TRY
    EXEC dbo.ValidateOrder @OrderId = 123;
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    THROW 50001, @ErrorMessage, 1;  -- Custom error number and state
END CATCH;

// This area would normally execute the above T‑SQL against a database.
# No live execution environment available.

RAISEERROR

The legacy RAISEERROR statement can generate custom error messages with severity and state.

RAISEERROR ('Invalid product price: %d', 16, 1, @Price);

Note: For new code, prefer THROW.

Full Example

CREATE PROCEDURE dbo.TransferFunds
    @FromAccount INT,
    @ToAccount   INT,
    @Amount      MONEY
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        BEGIN TRANSACTION;

        UPDATE dbo.Accounts
        SET Balance = Balance - @Amount
        WHERE AccountID = @FromAccount;

        IF @@ROWCOUNT = 0
            THROW 50002, 'Source account not found.', 1;

        UPDATE dbo.Accounts
        SET Balance = Balance + @Amount
        WHERE AccountID = @ToAccount;

        IF @@ROWCOUNT = 0
            THROW 50003, 'Destination account not found.', 1;

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

        DECLARE @ErrMsg NVARCHAR(4000) = ERROR_MESSAGE();
        DECLARE @ErrNum INT = ERROR_NUMBER();

        RAISERROR ('Transfer failed: %s (Error %d)', 16, 1, @ErrMsg, @ErrNum);
    END CATCH;
END;
GO

Best Practices

Prefer TRY…CATCH with THROW for new development.
Always check XACT_STATE() after an error to determine transaction status.
Use descriptive, custom error numbers (5‑digit range) to differentiate your errors.
Log errors using sys.sp_writeerrorlog or a centralized logging table.
Keep error handling logic close to the code that may generate the error.

Microsoft Docs