CODEWITHSUNDEEP
sql-serverstored-procedurestransactions
Weng Kit
Weng Kit

Reputation: 123

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?

Upvotes: 3

Views: 3512

Answers (1)

samithagun
samithagun

Reputation: 693

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

Upvotes: 3

Related Questions