title: SQL Transaction uncommittable while using try..catch.. Why?
body: <p>We're running into a problem here and I can't figure out why it behaves how it behaves.</p>
  
  <p>Given the following two (simplified) stored procedures in TSQL (SQL Server 2008R2)</p>
  
  <pre><code>create procedure [datetransaction1] 
  as
  begin
      begin try
          begin transaction
          declare @a datetime
          exec datetransaction2 '2013-02-02 22:21', @a output
          select @a
          exec datetransaction2 '2013-020222:22', @a output
          select @a
          exec datetransaction2 '2013-02-02 22:23', @a output
          select @a
  
          commit transaction
      end try
      begin catch
          print 'Catch'
      end catch
  end
  </code></pre>
  
  <p>and</p>
  
  <pre><code>create procedure [dbo].[datetransaction2] @text nvarchar(100), @res datetime OUTPUT  
  AS
  BEGIN 
      BEGIN TRY
          if (LEN(@text) = 16) SET @text = replace(@text, ' ', 'T') + ':00.000'
          else if (LEN(@text) = 19) SET @text = replace(@text, ' ', 'T') + '.000'
          else SET @text = replace(@text, ' ', 'T') 
          PRINT 'trydate:' + @text
          SELECT @res =convert(datetime, @text, 126)
      END TRY
      BEGIN CATCH
          PRINT ERROR_SEVERITY()
          PRINT 'errordate:' + @text
      END CATCH
  END
  </code></pre>
  
  <p>If you then execute <code>exec datetransaction1</code>, we see that all 3 calls to <code>datetransaction2</code> are executed, with the first and last (as expected) running correctly, and the second one entering the <code>CATCH</code> block within <code>datetransaction2</code>.</p>
  
  <p>So far, so good.</p>
  
  <p>But then we land in the catch block of <code>datetransaction1</code> with the message that the transaction is uncommittable:</p>
  
  <pre><code>Msg 266, Level 16, State 2, Procedure datetransaction1, Line 0
  Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
  Msg 3998, Level 16, State 1, Line 1
  Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
  </code></pre>
  
  <p>Which isn't supposed to happen (I think). We caught the errors in the sub procedures, so why would the transaction suddenly become uncommittable?</p>
  
  <p>Can someone explain that to me?</p>
  
  <p>Note that we can probably find a way around this, but I'm intrigued more by the idea behind it. Why does this transaction suddenly become uncommittable here?</p>