INF: Stored Procedures, Transactions, and Error 266

MORE INFORMATION

The following is a short code example that demonstrates the problem:

   CREATE PROCEDURE test AS
   SELECT @@trancount
   ROLLBACK TRANSACTION
   SELECT @@trancount
   GO
   BEGIN TRANSACTION
   EXEC test
   GO
				

Because @@trancount is not the same in both SELECT statements, error 266 is generated on return from the stored procedure.

This is expected behavior, but it does not mean that transactions cannot be started, completed, or aborted in a stored procedure. Instead, care must be taken so that the @@trancount global variable matches on both entry and exit of the stored procedure. This is documented in Transact-SQL under the topic Reference\T\Transactions\Rollback Transaction.

This problem is more likely to occur when writing nested stored procedures. However, there is solution so that the stored procedure works without the error. The following is a list of solutions, with sample code for each:

1. Perform final commit or rollback transactions from the same stored procedure nesting level where the transaction began, as shown by the following examples:
   

         -- Example 1.a
         CREATE PROCEDURE test1a AS
         SELECT @@trancount
         GO
         BEGIN TRANSACTION
         EXEC test1a
         ROLLBACK TRANSACTION
         GO
   
         -- Example 1.b
         CREATE PROCEDURE test1c AS
         SELECT @@trancount
         GO
         CREATE PROCEDURE test1b AS
         BEGIN TRANSACTION
         EXEC test1c
         COMMIT TRANSACTION
         GO
         EXEC test1b
         GO
   
   						

2. If nested transactions are used in a stored procedure, perform matching commits. Note the transaction is not committed until @@trancount is equal to 0 (zero).
   

         -- Example 2
         CREATE PROCEDURE test2b AS
         SELECT @@trancount
         BEGIN TRANSACTION
         SELECT @@trancount
         COMMIT TRANSACTION
         SELECT @@trancount
         GO
         CREATE PROCEDURE test2a AS
         BEGIN TRANSACTION
         EXEC test2b
         COMMIT TRANSACTION
         GO
         EXEC test2a
         GO
   
   						

3. If a rollback is needed and the stored procedure nesting level is different than where the transaction began, use RAISERROR, with a valid user-defined error, and check the @@error global variable after the EXECUTE command.
   

         -- Example 3
         USE master
         EXEC sp_addmessage 50001, 16, 'Rollback of transaction in test3'
         GO
         CREATE PROCEDURE test3 AS
         RAISERROR (50001,16,1)
         GO
         BEGIN TRANSACTION
         EXEC test3
         IF @@error <> 50001
         BEGIN
            PRINT 'Commit'
            COMMIT TRANSACTION
         END
         ELSE
         BEGIN
            PRINT 'Rollback'
            ROLLBACK TRANSACTION
         END
         GO
   
   						

4. The exception to this rule is that if a trigger performs a rollback, @@trancount needs not match its starting value, because the batch is aborted. However, a stored procedure called by a trigger may cause the problem if it aborted the transaction.

      -- Example 4
      CREATE TABLE x (col1 int)
      GO
      CREATE TRIGGER xins ON x FOR INSERT AS
      ROLLBACK TRANSACTION
      GO
      CREATE PROCEDURE sp_xinsert AS
      SELECT @@trancount
      INSERT x (col1) VALUES (1)
      SELECT @@trancount
      GO
      BEGIN TRANSACTION
      EXEC sp_xinsert
      IF @@error <> 0
      BEGIN
         PRINT 'Commit'
         COMMIT TRANSACTION
      END
      ELSE
      BEGIN
         PRINT 'Rollback'
         ROLLBACK TRANSACTION
      END
      GO
      SELECT * FROM x