Handle nested stored procedures

Transactions

A transaction is a block of SQL statements that usually modifies data. You can either commit or rollback a transaction. Transactions are useful when you want to make sure a chain of statements are completed together and if any of the statements fails, undo (rollback) all modifications performed.

For example; imagine when you want to change the order status on an order with data in two different tables

• Order Header
• Order Details

In this case you will only want to save (commit) your changes if all changes can be saved. If saving changes to one of the tables fails, you don't want to save anything and undo (rollback) everything.

This is where transactions comes to the rescue.

BEGIN TRANSACTION
   UPDATE OrderHeader
      SET Status = 'Completed'
   WHERE OrderId = 100

   UPDATE OrderDetails
      SET Status = 'Completed'
   WHERE OrderId = 100
COMMIT TRANSACTION

Stored Procedure to set Order status

Now lets embed the code to a separate stored procedure "spOrdersSetStatus". For the sake of best practice I will also introduce a TRY and CATCH block to handle any errors occured.

CREATE PROCEDURE spOrderSetStatus
  @OrderId INT,
  @Status NVARCHAR(25)
AS
BEGIN
   BEGIN TRY
      BEGIN TRANSACTION
         UPDATE OrderHeader
            SET Status = @Status
         WHERE OrderId = @OrderId

         UPDATE OrderDetails
            SET Status = @Status
         WHERE OrderId = @OrderId
      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        -- PUT SOME LOGGING HERE TO HANDLE YOUR ERROR
    END CATCH
END

The code will behave like this.

1. The code within the TRY block is run
2. BEGIN TRAN - A transaction is opened
3. If the code within the TRY block runs fine, COMMIT TRAN is done ( transaction closed) and the code ends.
4. If an error occurs within the TRY block, the compiler jumps to the CATCH block and continues.
5. ROLLBACK TRANSACTION is  run (transaction undo)
6. Code ends.

Reflections

The procedure above looks clean and solid and will work fine when called directly from an application.

However there are some major drawbacks if the procedure would be called from another stored procedure (nested stored procedure):

• No checking how many transactions are actually open. Closing more transactions than we've opened will result in error.
• No control if there is an active user transaction to commit, and in what state the transaction is in.

Nested Stored procedure

Now lets check what will happen if our stored procedure is called from another procedure.

Lets create another procedure to handle order completion:

CREATE PROCEDURE spOrderComplete
  @OrderId INT,
AS
BEGIN
   BEGIN TRY
      BEGIN TRANSACTION
         EXEC spOrderSetStatus 
            @OrderId = @OrderId,
            @Status = 'Complete';

        EXEC spOrderSendReport 
            @OrderId = @OrderId;

        EXEC spOrderClose 
            @OrderId = @OrderId;

      COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION
        -- PUT SOME LOGGING HERE TO HANDLE YOUR ERROR
    END CATCH
END

This code will 

1. BEGIN TRAN - Open transaction 1
   1. Run the spOrderSetStatus procedure
   2. Run the spOrderSendReport procedure
   3. Run the spOrderClose procedure
2. COMMIT TRAN - Close transaction 1

What will now happen if an error occurs in spOrderSetStatus?

1. BEGIN TRAN - Open transaction 1
   1. Run the spOrderSetStatus procedure
      1. BEGIN TRAN - Open transaction 2
         1. Code Runs
         2. Error Occurs
         3. All transactions are rolled back and we now have 0 open transactions.
2. Error spotted in the TRY block, code jumps to CATCH block.
3. ROLLBACK TRAN - Trying to rollback and close transactions but we have 0 open
4. ERROR throw due to no open transactions.

What we need is a way to keep count of open transactions before we close any transactions.

Local transactions

There is actually a kind of local transaction or savestate, that we could use. The syntax for opening a local transaction is SAVE TRANSACTION <yourTransactionName>. This could come really handy in our example.

We will also need a way to check if there already are any open transactions when deciding to use local or global transactions. The variable @@trancount is a perfect match and will give us the number of open transactions.

CREATE PROCEDURE spOrderSetStatus
  @OrderId INT,
  @Status NVARCHAR(25)
AS
BEGIN
    DECLARE @hasOpenTran BIT = 'False';

   BEGIN TRY
        IF @@trancount = 0
        BEGIN
            BEGIN TRANSACTION
            SET @hasOpenTran = 'True';
        END
        ELSE
            SAVE TRANSACTION spMyLocalTran

        UPDATE OrderHeader
            SET Status = @Status
        WHERE OrderId = @OrderId

        UPDATE OrderDetails
            SET Status = @Status
        WHERE OrderId = @OrderId

        IF @hasOpenTran = 'True'
            COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @hasOpenTran = 'True'
            ROLLBACK TRANSACTION
        ELSE
            IF XACT_STATE() <> -1
                ROLLBACK TRANSACTION spMyLocalTran
                
        -- PUT SOME LOGGING HERE TO HANDLE YOUR ERROR
    END CATCH
END

This procedure will now gracefully only close transactions opened locally by this stored procedure, and you will avoid any errors due to mismatch between opened and closed transactione.