Stored procedure transaction patterns with `TRY...CATCH`, output parameters, and error handling

Overview

Stored procedures often contain multi-step database operations that must succeed or fail as one unit. SQL Server provides transactions, TRY...CATCH, XACT_STATE(), @@TRANCOUNT, output parameters, return codes, THROW, and error functions to help procedures handle success, failure, and caller communication.

The hard part is not writing BEGIN TRY. The hard part is designing a procedure that starts and ends transactions correctly, does not commit work it should roll back, preserves the original error, returns useful output values, and behaves predictably when called inside an existing transaction.

This topic matters because stored procedures are still common in enterprise systems, reporting workflows, legacy applications, data imports, background jobs, and SQL Server-heavy architectures. A weak transaction pattern can leave data partially updated, hide errors from the application, or make production incidents much harder to diagnose.

For interviews, strong candidates can describe a safe transaction template, explain XACT_STATE() and @@TRANCOUNT, choose THROW over legacy patterns for rethrowing, use output parameters intentionally, and avoid swallowing errors.

Core Concepts

Stored Procedure Responsibilities

A stored procedure can encapsulate:

• Validation.
• Multi-table writes.
• Transaction boundaries.
• Error handling.
• Return values and output parameters.
• Security boundaries.
• Data-access contracts for application code.

Example:

CREATE PROCEDURE dbo.CreateOrder
    @CustomerId BIGINT,
    @OrderId BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.Orders (CustomerId, CreatedAt)
    VALUES (@CustomerId, SYSUTCDATETIME());

    SET @OrderId = CONVERT(BIGINT, SCOPE_IDENTITY());
END;

This is simple because it performs one insert. More realistic procedures often need transaction handling and error handling.

TRY...CATCH Basics

TRY...CATCH catches many runtime errors inside T-SQL.

Basic shape:

BEGIN TRY
    -- Work that may fail.
END TRY
BEGIN CATCH
    -- Error handling.
    THROW;
END CATCH;

Inside CATCH, SQL Server exposes error details through functions:

• ERROR_NUMBER()
• ERROR_SEVERITY()
• ERROR_STATE()
• ERROR_PROCEDURE()
• ERROR_LINE()
• ERROR_MESSAGE()

Example:

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;

    THROW;
END CATCH;

For production procedures, logging the error can be useful, but swallowing it is usually a bug.

Transaction Basics

A transaction groups work into an all-or-nothing unit.

Example:

BEGIN TRANSACTION;

INSERT dbo.Orders (CustomerId, CreatedAt)
VALUES (@CustomerId, SYSUTCDATETIME());

INSERT dbo.OrderEvents (OrderId, EventName, CreatedAt)
VALUES (SCOPE_IDENTITY(), N'Created', SYSUTCDATETIME());

COMMIT TRANSACTION;

If a failure happens between the two inserts, the procedure needs to roll back. That is why transaction logic is usually combined with TRY...CATCH.

A Basic Transaction Pattern

Example:

CREATE PROCEDURE dbo.CreateOrder
    @CustomerId BIGINT,
    @OrderId BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        INSERT dbo.Orders (CustomerId, CreatedAt)
        VALUES (@CustomerId, SYSUTCDATETIME());

        SET @OrderId = CONVERT(BIGINT, SCOPE_IDENTITY());

        INSERT dbo.OrderEvents (OrderId, EventName, CreatedAt)
        VALUES (@OrderId, N'Created', SYSUTCDATETIME());

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

        THROW;
    END CATCH;
END;

This pattern:

• Starts a transaction.
• Commits only after all work succeeds.
• Rolls back when a transaction is still active or uncommittable.
• Rethrows the original error.
• Uses SET XACT_ABORT ON to make many runtime errors abort the transaction.

XACT_STATE

XACT_STATE() tells whether the current session has an active transaction and whether it can be committed.

Common values:

• 1: active and committable transaction.
• 0: no active transaction.
• -1: active but uncommittable transaction.

Inside CATCH, this matters because not every failed transaction can be committed.

Example:

BEGIN CATCH
    IF XACT_STATE() = -1
    BEGIN
        ROLLBACK TRANSACTION;
    END;
    ELSE IF XACT_STATE() = 1
    BEGIN
        ROLLBACK TRANSACTION;
    END;

    THROW;
END CATCH;

Most procedure templates simply roll back when XACT_STATE() <> 0, but understanding the difference is useful in interviews.

@@TRANCOUNT And Nested Procedure Calls

@@TRANCOUNT returns the number of active BEGIN TRANSACTION statements for the current session.

This matters when a stored procedure may be called inside an existing transaction.

Problem pattern:

CREATE PROCEDURE dbo.DoWork
AS
BEGIN
    BEGIN TRANSACTION;

    -- Work

    COMMIT TRANSACTION;
END;

If the caller already has a transaction, this procedure increments the transaction count and then commits one level. It does not truly commit all outer work, but it does make transaction ownership confusing.

Safer pattern:

DECLARE @StartedTransaction BIT = 0;

IF @@TRANCOUNT = 0
BEGIN
    SET @StartedTransaction = 1;
    BEGIN TRANSACTION;
END;

BEGIN TRY
    -- Work

    IF @StartedTransaction = 1
    BEGIN
        COMMIT TRANSACTION;
    END;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 AND @StartedTransaction = 1
    BEGIN
        ROLLBACK TRANSACTION;
    END;

    THROW;
END CATCH;

This pattern makes transaction ownership explicit. If the procedure started the transaction, it finishes it. If the caller owns the transaction, the procedure does not commit it.

Savepoints For Caller-Owned Transactions

When a procedure is called inside an existing transaction, savepoints can let the procedure roll back its own work without rolling back the caller's entire transaction.

Example:

DECLARE @StartedTransaction BIT = 0;

IF @@TRANCOUNT = 0
BEGIN
    SET @StartedTransaction = 1;
    BEGIN TRANSACTION;
END
ELSE
BEGIN
    SAVE TRANSACTION BeforeProcedureWork;
END;

BEGIN TRY
    -- Procedure work here.

    IF @StartedTransaction = 1
    BEGIN
        COMMIT TRANSACTION;
    END;
END TRY
BEGIN CATCH
    IF XACT_STATE() = -1
    BEGIN
        ROLLBACK TRANSACTION;
    END
    ELSE IF @StartedTransaction = 1
    BEGIN
        ROLLBACK TRANSACTION;
    END
    ELSE IF XACT_STATE() = 1
    BEGIN
        ROLLBACK TRANSACTION BeforeProcedureWork;
    END;

    THROW;
END CATCH;

Savepoints add complexity, so they should be used when the procedure truly needs to participate in caller-owned transactions. Many teams instead establish a simpler rule: procedures either own their transactions or require the caller to own them, but not both.

SET XACT_ABORT ON

SET XACT_ABORT ON tells SQL Server to automatically roll back the current transaction when many runtime errors occur.

Example:

SET XACT_ABORT ON;

This is commonly used in stored procedures that perform transactional writes. It reduces the chance that a runtime error leaves a transaction open or partially committable.

Important nuance:

• THROW honors XACT_ABORT.
• RAISERROR does not behave the same way with XACT_ABORT.
• You still need TRY...CATCH to log, clean up, and rethrow.

THROW Vs RAISERROR

THROW is the modern way to raise or rethrow errors in T-SQL.

Rethrow original error:

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

    THROW;
END CATCH;

Using THROW; without arguments inside CATCH preserves the original error details.

Custom error:

THROW 51000, 'Customer is not active.', 1;

RAISERROR still exists in old code, but for new procedure error handling, THROW is usually preferred because it integrates better with modern error handling and preserves original errors cleanly when rethrowing.

Output Parameters

Output parameters let a stored procedure return scalar values to the caller.

Example:

CREATE PROCEDURE dbo.CreateCustomer
    @Email NVARCHAR(320),
    @CustomerId BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT dbo.Customers (Email, CreatedAt)
    VALUES (@Email, SYSUTCDATETIME());

    SET @CustomerId = CONVERT(BIGINT, SCOPE_IDENTITY());
END;

Caller:

DECLARE @NewCustomerId BIGINT;

EXEC dbo.CreateCustomer
    @Email = N'[email protected]',
    @CustomerId = @NewCustomerId OUTPUT;

SELECT @NewCustomerId AS NewCustomerId;

Output parameters are good for:

• New identity values.
• Status codes.
• Row counts.
• Calculated totals.
• Simple messages or flags.

Do not use many output parameters to return relational result sets. Use result sets for rows.

Return Codes Vs Output Parameters Vs Result Sets

SQL Server procedures can communicate in several ways:

• Result sets: return rows to the caller.
• Output parameters: return named scalar values.
• Return code: return an integer status.
• Errors: signal failure through exceptions.

Practical guidance:

• Use result sets for data.
• Use output parameters for scalar outputs.
• Use return codes sparingly for simple status values.
• Use errors for failure.
• Do not return "success with hidden error message" when the operation actually failed.

Example:

CREATE PROCEDURE dbo.TryReserveInventory
    @ProductId BIGINT,
    @Quantity INT,
    @ReservationId BIGINT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    -- Procedure either succeeds and sets @ReservationId,
    -- or throws an error that the caller must handle.
END;

Error Logging

Error logging should capture enough information to diagnose the failure without hiding the failure.

Example:

BEGIN CATCH
    DECLARE
        @ErrorNumber INT = ERROR_NUMBER(),
        @ErrorProcedure SYSNAME = ERROR_PROCEDURE(),
        @ErrorLine INT = ERROR_LINE(),
        @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();

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

    INSERT dbo.ErrorLog (ErrorNumber, ErrorProcedure, ErrorLine, ErrorMessage, CreatedAt)
    VALUES (@ErrorNumber, @ErrorProcedure, @ErrorLine, @ErrorMessage, SYSUTCDATETIME());

    THROW;
END CATCH;

Important caution: if the transaction is uncommittable, logging inside the same transaction may fail or be rolled back. Some systems log errors outside the failed transaction, in application code, through separate logging procedures, or in external observability tools.

Idempotency And Retries

Stored procedures that are called by applications or background jobs should be designed with retries in mind.

If the application retries after a timeout, did the first execution commit or roll back? If the procedure creates an order, charges a customer, or reserves inventory, blindly retrying can create duplicate side effects.

Good patterns include:

• Use idempotency keys for operations that may be retried.
• Enforce unique constraints on idempotency keys.
• Return existing results for repeated requests.
• Keep transactions short.
• Make errors explicit.
• Let the application distinguish retryable and non-retryable failures.

Common Mistakes

Common mistakes include:

• Starting a transaction and forgetting to roll it back in CATCH.
• Committing in CATCH without checking transaction state.
• Swallowing errors and returning a success code.
• Using RAISERROR by habit instead of THROW for new code.
• Losing the original error line and message.
• Ignoring @@TRANCOUNT when procedures are nested.
• Leaving transactions open after errors.
• Logging inside an uncommittable transaction without testing.
• Using output parameters for large result sets.
• Setting output parameters before rollback and assuming they prove success.

Best Practices

Best practices:

• Use SET NOCOUNT ON in procedures unless rowcount messages are intentionally needed.
• Use SET XACT_ABORT ON for transactional write procedures.
• Keep transactions short.
• Start and commit only transactions the procedure owns.
• Use XACT_STATE() in CATCH.
• Use THROW; to preserve the original error.
• Use output parameters for clear scalar results.
• Return rows as result sets, not many output parameters.
• Do not hide real failures behind status codes.
• Test success, expected failure, deadlock, timeout, and nested-transaction scenarios.