T-SQL Error Handling with TRY...CATCH and THROW

I've spent more evenings than I'd like hunting for the procedure that left a transaction open, swallowed an error, or reported "an error occurred" without saying which one. T-SQL has had structured TRY...CATCH since SQL Server 2005 and the THROW statement since 2012, and together with SET XACT_ABORT ON and XACT_STATE() they cover almost every case. The trouble is that the pieces interact in non-obvious ways: some errors do not enter the CATCH block at all, some leave the transaction in a state where you can only roll back, and re-raising loses the original line number unless you do it right.

Here's the pattern I use for every procedure that writes data, what each part does, and the cases where it still can't help.

The basic TRY...CATCH shape

BEGIN TRY
    INSERT dbo.Orders (CustomerId, OrderDate) VALUES (42, SYSDATETIME());
    UPDATE dbo.Customers SET LastOrderDate = SYSDATETIME() WHERE CustomerId = 42;
END TRY
BEGIN CATCH
    SELECT ERROR_NUMBER() AS number, ERROR_SEVERITY() AS severity, ERROR_STATE() AS state,
           ERROR_PROCEDURE() AS proc_name, ERROR_LINE() AS line, ERROR_MESSAGE() AS message;
END CATCH;

Any error with severity 11 to 19 inside the TRY block jumps to CATCH. The ERROR_* functions return details of that error and are valid only inside CATCH. Severity 10 and lower (informational, PRINT) does not trigger it; severity 20 and above terminates the connection and never reaches CATCH.

XACT_ABORT and the transaction

I turn on XACT_ABORT in every procedure that writes. Without SET XACT_ABORT ON, some errors (such as a constraint violation) leave the transaction open and usable, while others (such as a deadlock, or a conversion error in some contexts) doom it. XACT_ABORT ON makes every runtime error doom the transaction, which is predictable, and also ensures that a client timeout rolls back rather than leaving locks held.

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

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT dbo.Orders (CustomerId, OrderDate, TotalAmount)
        VALUES (@CustomerId, SYSDATETIME(), @Amount);

        UPDATE dbo.Customers SET LastOrderDate = SYSDATETIME() WHERE CustomerId = @CustomerId;
        IF @@ROWCOUNT = 0
            THROW 50001, 'Customer does not exist.', 1;

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

XACT_STATE() returns 1 for an active committable transaction, -1 for an active but uncommittable (doomed) one, and 0 for none. Checking <> 0 before ROLLBACK avoids the "Msg 3903: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION" error when the transaction was already rolled back. CREATE OR ALTER is available since SQL Server 2016 SP1.

THROW: re-raising and raising your own

A bare THROW; inside CATCH re-raises the original error with its original number, message, severity, state, procedure and line. That is the single biggest reason to prefer it over RAISERROR, which can only raise a new error and therefore always reports the RAISERROR line.

THROW with arguments raises a user error. The number must be 50000 or higher, the severity is always 16, and unlike RAISERROR it does not support printf-style formatting, so build the message first:

DECLARE @msg NVARCHAR(2048) = FORMATMESSAGE('Customer %d not found.', @CustomerId);
THROW 50001, @msg, 1;

FORMATMESSAGE with a literal format string works since SQL Server 2016. The statement before THROW must end with a semicolon; otherwise the parser treats THROW as an alias and you get a confusing syntax error.

Logging before re-throwing

For production procedures I write the error to a table, then re-throw so the caller still fails. The catch is that if the transaction is doomed, the INSERT into the log table fails too, so roll back first:

BEGIN CATCH
    DECLARE @num INT = ERROR_NUMBER(), @m NVARCHAR(4000) = ERROR_MESSAGE(),
            @p NVARCHAR(128) = ERROR_PROCEDURE(), @l INT = ERROR_LINE();
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;

    INSERT dbo.ErrorLog (ErrorNumber, ErrorMessage, ProcedureName, LineNumber, LoggedAt, LoginName)
    VALUES (@num, @m, @p, @l, SYSDATETIME(), ORIGINAL_LOGIN());

    THROW;
END CATCH;

Capture the ERROR_* values into variables first; after the INSERT succeeds they still return the original error, but capturing first is cheaper to reason about.

Nested procedures

When a procedure with its own TRY...CATCH calls another that also has one, the inner one catches and re-throws, and the outer one catches the re-thrown error. Nested BEGIN TRANSACTION increments @@TRANCOUNT but a single ROLLBACK undoes everything, so the inner CATCH rolling back can leave the outer procedure with @@TRANCOUNT = 0 unexpectedly. The pattern above handles this because it checks XACT_STATE() rather than assuming; just do not COMMIT in the outer procedure without also checking @@TRANCOUNT > 0.

Testing it

EXEC dbo.PlaceOrder @CustomerId = 999999, @Amount = 10;   -- expect error 50001, transaction rolled back
SELECT @@TRANCOUNT AS open_transactions;                -- expect 0

EXEC dbo.PlaceOrder @CustomerId = 42, @Amount = NULL;    -- expect 515 Cannot insert the value NULL, re-thrown with original line
SELECT TOP 5 * FROM dbo.ErrorLog ORDER BY LoggedAt DESC;

@@TRANCOUNT returning 0 after a failed call is the test that matters; a lingering 1 means locks are held until the connection closes.

Where the pattern doesn't help

Compile-time errors bypass CATCH. "Msg 208: Invalid object name" from a table that does not exist at compile time in the same scope, and syntax errors, do not enter CATCH in the batch where they occur. They are caught only by a TRY...CATCH in the calling procedure or batch. Deferred name resolution is why this feels inconsistent.

"Msg 3930: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction." You tried to do work in CATCH while XACT_STATE() was -1. Roll back first, as in the logging example.

"Msg 266: Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements." A procedure changed @@TRANCOUNT relative to entry. Usually an inner ROLLBACK followed by the outer COMMIT being skipped, or vice versa. Check @@TRANCOUNT before COMMIT.

Errors from RAISERROR with severity below 11 are not caught. RAISERROR('note', 10, 1) is a message, not an error. Use severity 16 if you want it caught.

About eight lines of boilerplate, and "something went wrong" turns into the exact error, procedure and line. I paste it into every new procedure.