Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

Without error handling, a failed statement inside a transaction can leave the transaction open: the rest of the batch carries on, locks stay held, and the connection goes back to the pool with work half done. The pattern below is the one to use in every procedure that changes data.

Why the default behaviour is dangerous

Most errors only end the statement, not the transaction. The transaction stays open with some changes made and some not.

CREATE TABLE dbo.Account (ID int PRIMARY KEY, Balance money CHECK (Balance >= 0));
INSERT dbo.Account VALUES (1, 100), (2, 0);

BEGIN TRAN;
    UPDATE dbo.Account SET Balance = Balance + 150 WHERE ID = 2;  -- works
    UPDATE dbo.Account SET Balance = Balance - 150 WHERE ID = 1;  -- fails the CHECK constraint
COMMIT;   -- still runs: account 2 got 150 from nowhere

SELECT * FROM dbo.Account;

The standard pattern

CREATE OR ALTER PROCEDURE dbo.TransferMoney
    @FromID int, @ToID int, @Amount money
AS
SET NOCOUNT ON;
SET XACT_ABORT ON;   -- any run-time error dooms the transaction and ends the batch

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE dbo.Account SET Balance = Balance - @Amount WHERE ID = @FromID;
    UPDATE dbo.Account SET Balance = Balance + @Amount WHERE ID = @ToID;

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

    -- log the error here if you keep an error table, then re-raise it unchanged
    THROW;
END CATCH;
GO

EXEC dbo.TransferMoney @FromID = 1, @ToID = 2, @Amount = 500;  -- fails, nothing changes

• SET XACT_ABORT ON makes almost every error roll back the whole transaction, including client timeouts, which TRY/CATCH never sees because the client simply stops the batch.
• IF @@TRANCOUNT > 0 before ROLLBACK avoids error 3903 when there is no transaction left to roll back.
• THROW with no arguments re-raises the original error number, message and line, so the caller and the SQL Agent job history see the real error. RAISERROR changes the error number to 50000.
• The statement before THROW must end with a semicolon.

XACT_STATE()

Inside the CATCH block, XACT_STATE() says what can still be done with the transaction:

• 1: the transaction is open and can be committed (only possible with XACT_ABORT OFF).
• -1: the transaction is doomed. It can only be rolled back. Any statement that writes to the log fails with error 3930.
• 0: there is no transaction.

SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
    UPDATE dbo.Account SET Balance = -1 WHERE ID = 1;   -- CHECK constraint error
    COMMIT;
END TRY
BEGIN CATCH
    SELECT XACT_STATE() AS xact_state, @@TRANCOUNT AS trancount,
           ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;
    IF XACT_STATE() <> 0 ROLLBACK;
END CATCH;

With XACT_ABORT ON, XACT_STATE() is always -1 or 0 in the CATCH block, which is what you want: there is no half-done transaction to commit by mistake.

Errors TRY/CATCH does not catch

• Compile errors and missing objects in the same batch (deferred name resolution errors are caught one level up, in the calling procedure).
• Errors with severity 10 or lower (these are messages, not errors) and severity 20 or higher that end the connection.
• Attention events: a client timeout or a cancelled query ends the batch without running the CATCH block. Only XACT_ABORT ON makes sure the transaction is rolled back.
• KILL of the session.

Calling a procedure from inside a transaction

If the caller already has a transaction open, the inner procedure’s ROLLBACK rolls back the caller’s work too, and the caller gets error 266 about a mismatched @@TRANCOUNT. The simplest rule: the code that begins the transaction is the code that commits or rolls it back, and inner procedures just THROW.

From my SQL Server DBA handbook.