TIP: Nested Stored Procedure Calls with SQL Server Transactions

When developing data-driven software, it is often vital that you manage transactions to ensure that unexpected errors do not corrupt the database. Transactions allow you to group sets of related database calls in a single, logical unit-of-work. The classic example of debiting one bank account, then crediting another, is often used to illustrate the importance of transactions. If the process blows up somewhere in the middle, you want everything put back as it was initially. It is the 'all or nothing' principle.

For Microsoft developers who write applications against a SQL Server 2005 database, it is common to write transaction logic in C#, using the System.Data.SqlClient.SqlTransaction class that wraps calls to SQL Server statements or stored procedures. Another option is to utilize MTS (Microsoft Transaction Server) to support distributed transactions. When these approaches are used, writing T-SQL code to manage transactions typically can be avoided. Sometimes, however, it makes the most sense to manage transactions at the stored procedure level. For example, you might choose to implement a process that requires numerous, process-intensive queries and data manipulation statements as a database stored procedure or a set of procedures. This tip will show you how to avoid transaction-related errors when nesting procedure calls in SQL Server.

In its simplest form, here is a SQL Server stored procedure that manages a transaction:

CREATE PROCEDURE [dbo].[simple_proc]
AS
BEGIN
   BEGIN TRY
      BEGIN TRANSACTION;

      PRINT 'Executing simple proc.'
      --Execute logic within transaction...

      COMMIT TRANSACTION;
   END TRY

   BEGIN CATCH
      ROLLBACK TRANSACTION;
      RAISERROR ('Error in simple proc!',16,1)
   END CATCH
END

A transaction is started. If things go well, changes are committed in the database. Otherwise, the changes are rolled back. This works fine in most situations. That is, unless you have an environment in which you have other stored procedures that invoke this one and they too manage transactions. In such an environment, problems can arise because, though each 'BEGIN TRANSACTION' increments @@TRANCOUNT by one and each 'COMMIT TRANSACTION' decrements the count, a 'ROLLBACK TRANSACTION' rolls back all changes. That is, a rollback sets @@TRANCOUNT all the way back to zero. Attempting to execute a second rollback will generate an error with the message 'The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.'.

This situation can occur if 'simple_proc' invokes a second procedure, 'another_simple_proc', but 'another_simple_proc' can also be called on its own. To account for the later scenario, 'another_simple_proc' must manage its own transactions instead of letting a caller do it for him. Because, in reality, it is not feasible to always know which procedure will be the outermost procedure in the chain, a certain amount of flexibility is necessary. So, some logic must be written to deal with this.

Next is an updated version of simple proc with some additional logic.

CREATE PROCEDURE [dbo].[simple_proc]
AS
BEGIN

   --Holds info on whether or not this proc originated the
   --transaction.
   DECLARE @v_trans_started                  BIT

   BEGIN TRY
      PRINT 'Executing simple proc.'

      SET @v_trans_started = 0
      IF @@TRANCOUNT = 0
         BEGIN
            BEGIN TRANSACTION;
            SET @v_trans_started = 1
         END
      ELSE
         SET @v_trans_started = 0


      --Execute logic within transaction...



      IF @v_trans_started = 1
         BEGIN
            SET @v_trans_started = 0
            COMMIT TRANSACTION;
         END

   END TRY

   BEGIN CATCH

      IF @v_trans_started = 1
         BEGIN
            SET @v_trans_started = 0
            ROLLBACK TRANSACTION;
         END
      RAISERROR ('Error in simple proc!',16,1)

   END CATCH
END

Page 1 of 2