title: SQL Server Nested transaction in Stored Procedure
body: <p>I'm using SQL Server 2014; my stored procedure will be nested transaction procedure, whereby it will call few stored procedures that have transaction in them. If either one of the inner stored procedures hits an error, then will rollback all, such as </p>
  
  <pre><code>Begin Try
  Begin Tran
     Exec Stored Proc 1 (with begin tran inside)
     Exec Stored Proc 2 (with begin tran inside)
     Exec Stored Proc 3 (with begin tran inside)
     Exec Stored Proc 4 (with begin tran inside)
     Exec Stored Proc 5 (with begin tran inside)   
  Commit Tran
  End Try
  Begin Catch
     Catch exception then roll back tran
  End Catch
  </code></pre>
  
  <p>The problem is the transaction count after execute the inner stored procedures are mismatched, however if I didn't open a transaction in the inner stored procedure, it won't rollback. Can anyone give me some suggestions?</p>