Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server Nested transaction in Stored Procedure

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?

like image 680
Weng Kit Avatar asked Jun 05 '26 03:06

Weng Kit


1 Answers

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

like image 130
samithagun Avatar answered Jun 07 '26 22:06

samithagun



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!