title: SQL - Transaction count after EXECUTE indicates a mismatch
body: <p>I am working on a stored procedure in MS SQL Server Management Studio 2012.</p>
  
  <p>I am reusing some code from other procedures and this includes calling another stored procedure with the <code>EXEC</code> command.</p>
  
  <p>This procedure is going to take information for an object as parameters, the reset that object's status, and then look for related (downstream) objects and reset their statuses as well.</p>
  
  <p>The code was working but required a small change to reset and related object's status even if it was not in an error status. To address this issue, I created a variable before the main loop that iterates over the related objects, and then when the first object that has an error status is encountered, it sets the value of the variable. The variable itself is a <code>BIT</code>.</p>
  
  <p>Here is the procedure:</p>
  
  <pre><code>USE [DB_TEST]
  GO
  
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER          PROC [dbo].[resetTasksInErrorStatus]
      @TaskId                         INT,
      @SubjectId                      INT,
      @ProjectProtocolId              INT,
      @TimePointId                    INT,
      @WorkbookId                     INT,
      @LogCreatorUserId               INT,
      @LogReasonTypeId                INT,
      @LogOtherReason                 VARCHAR(256),
      @SignatureCaptured              BIT,
      @ManualChangeTaskStatus         BIT,
      @ErrorIfModifiedAfter           DATETIME,       
      @NewTaskStatusID                INT,            
      @ProcessorUserId                INT
  
  AS
  BEGIN
      SET NOCOUNT ON;
  
      BEGIN TRANSACTION ResetTasksInError
  
      -- Make sure the task has not been modified since the reset was requested
      IF(EXISTS(
          SELECT ti.* FROM TaskInstance as ti
          WHERE ti.ModifyDate &gt; @ErrorIfModifiedAfter
              AND ti.TaskId = @TaskId
              AND ti.SubjectId = @SubjectId
              AND ti.ProjectProtocolId = @ProjectProtocolId
              AND ti.TimePointId = @TimePointId
              AND ti.WorkbookId = @WorkbookId))
      BEGIN
          RAISERROR('The task to be reset was modified before the reset could complete', 16, 1);
          GOTO error
      END
  
      -- Get all downstream task instances
      SELECT *
      INTO #downstreamTaskInstances
      from dbo.fnGetTaskInstancesDownstream
      (
          @TaskId,
          @SubjectId,
          @TimePointId
      )
  
      -- Get the previous task status
      DECLARE @OldTaskStatus INT;
      SELECT TOP 1 @OldTaskStatus = ti.TaskStatusTypeId FROM TaskInstance as ti
      WHERE ti.TaskId = @TaskId
          AND ti.SubjectId = @SubjectId
          AND ti.TimePointId = @TimePointId
  
      -- Reset the task
      EXEC setTaskStatus
          @TaskID,
          @SubjectId,
          @ProjectProtocolId,
          @TimePointId,
          @WorkBookId,
          @OldTaskStatus,
          @NewTaskStatusID,
          @ProcessorUserId,
          @LogCreatorUserId,
          @LogReasonTypeId,
          @LogOtherReason,
          @SignatureCaptured,
          @ManualChangeTaskStatus,
          NULL,
          NULL,
          NULL,
          1
  
      -- Check if setTaskStatus rolled back our transaction
      IF(@@TRANCOUNT = 0)
      BEGIN
          RAISERROR('Error in sub procedure. Changes rolled back.', 16, 1);
          RETURN
      END
  
      -- Set a boolean variable to determine whether downstream tasks should be reset
      DECLARE @ResetDownstreamTasks BIT = 0;
      --Set @ResetDownstreamTasks = 0;
  
      -- Create a cursor of the downstream tasks
      DECLARE downstreamCursor CURSOR FOR
          SELECT TaskId, TimePointId, ProcessorUserId, TaskStatus, WorkBookId, ProjectProtocolId, IsManual, TaskStatus
          FROM #downstreamTaskInstances
      OPEN downstreamCursor
  
      -- Reset each downstream task to unprocessed
      DECLARE @CursorTaskID INT;
      DECLARE @CursorTimePointID INT;
      DECLARE @CursorProcessorUserId INT;
      DECLARE @CursorTaskStatusID INT;
      DECLARE @CursorWorkBookId INT;
      DECLARE @CursorProjectProtocolId INT;
      DECLARE @CursorIsManual BIT;
      Declare @CursorTaskStatus INT;
      FETCH NEXT FROM downstreamCursor INTO @CursorTaskID, @CursorTimePointId, @CursorProcessorUserId, @CursorTaskStatusID, @CursorWorkBookId, @CursorProjectProtocolId, @CursorIsManual, @CursorTaskStatus
      WHILE @@FETCH_STATUS = 0 AND @@ERROR = 0 AND @@TRANCOUNT = 1
      BEGIN
          -- Check if the task is in error status, and then make sure it is not an manual task.
          -- Manual tasks should never be in error status, so there is no need to reset them.
          if @CursorTaskStatus = 10 and @CursorIsManual &lt;&gt; 1
          begin
                  SET @ResetDownstreamTasks = 1;
  
                  EXEC setTaskStatus
                      @CursorTaskID,
                      @SubjectId,
                      @CursorProjectProtocolId,
                      @CursorTimePointId,
                      @CursorWorkBookId,
                      @CursorTaskStatusID,
                      1, -- Unprocessed
                      @CursorProcessorUserId,
                      @LogCreatorUserId,
                      @LogReasonTypeId,
                      @LogOtherReason,
                      @SignatureCaptured,
                      @ManualChangeTaskStatus,
                      NULL,
                      NULL,
                      NULL,
                      0
          end;
  
  
          if @ResetDownstreamTasks = 1
          begin
              EXEC setTaskStatus
                  @CursorTaskID,
                  @SubjectId,
                  @CursorProjectProtocolId,
                  @CursorTimePointId,
                  @CursorWorkBookId,
                  @CursorTaskStatusID,
                  6, -- Inspected
                  @CursorProcessorUserId,
                  @LogCreatorUserId,
                  @LogReasonTypeId,
                  @LogOtherReason,
                  @SignatureCaptured,
                  @ManualChangeTaskStatus,
                  NULL,
                  NULL,
                  NULL,
                  0
          end
  
        FETCH NEXT FROM downstreamCursor INTO @CursorTaskID, @CursorTimePointId, @CursorProcessorUserId, @CursorTaskStatusID, @CursorWorkBookId, @CursorProjectProtocolId, @CursorIsManual, @CursorTaskStatus
      END
  
      DROP TABLE #downstreamTaskInstances
      CLOSE downstreamCursor
      DEALLOCATE downstreamCursor
  
      -- Check if setTaskStatus rolled back our transaction
      IF(@@TRANCOUNT = 0)
      BEGIN
          RAISERROR('Error in sub procedure. Changes rolled back.', 16, 1);
          RETURN
      END
  
      IF(@@ERROR &lt;&gt; 0)
      BEGIN
          GOTO ERROR
      END
  
      COMMIT TRANSACTION  ResetTasksInError
      RETURN
  
      ERROR:
          RAISERROR('Error encountered. Changes rolled back.', 16,1);
          ROLLBACK TRANSACTION ResetTasksInError
          RETURN
  END
  </code></pre>
  
  <p>When I run the procedure I get these errors:</p>
  
  <pre><code>Msg 266, Level 16, State 2, Procedure setTaskStatus, Line 0
  Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.
  Msg 16943, Level 16, State 4, Procedure resetTasksInErrorStatus, Line 197
  Could not complete cursor operation because the table schema changed after the cursor was declared.
  Msg 3701, Level 11, State 5, Procedure resetTasksInErrorStatus, Line 200
  Cannot drop the table '#downstreamTaskInstances', because it does not exist or you do not have permission.
  Msg 50000, Level 16, State 1, Procedure resetTasksInErrorStatus, Line 207
  Error in sub procedure. Changes rolled back.
  </code></pre>
  
  <p>If I comment out the <code>SET ...</code> statement the procedure runs and works (not as desired).</p>
  
  <p>I have looked around for similar questions, but none of them solved my problem.</p>
  
  <p>Is there something I am missing with regards to the SET statement?</p>
  
  <p>Is it affecting the <code>@@TRANCOUNT</code> variable somehow?</p>
  
  <p>I did see some posts around that mentioned the problem is likely to be in the stored procedure that this one calls, but I am a little hesitant because that stored procedure works, and these errors only show up when trying to set the value of the variable.</p>