SQL Server Nested transaction in Stored Procedure

Question

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

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

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?

Inside each procedure use a try catch. Roll back the transaction in the inner procedure and return an error to the outer proc. In the outer proc, you get an error from any inner proc break the cycle of calling each transaction and roll back — S3S
hi @scsimon, thank for your suggestion. let's say, if store proc 1 and 2 execute successfully, but stored proc 3 hit error. So the outer catch and rollback will rollback stored proc 1 and 2 too?? — Weng Kit

samithagun samithagun · Accepted Answer · 2017-06-05T04:18:53

Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed.

Nesting Transactions

SQL Server Nested transaction in Stored Procedure

1 Answers