How to use transactions in multiple SQL procedures?

0
votes

I would like to start a transaction through a sql procedure, run other 2 procedure, and then run the first procedure with command: 'commit'. Do you believe that this could be possible? I tried but received an error. Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

2
sqlsqltransaction
possible duplicate of Nested stored procedures containing TRY CATCH ROLLBACK pattern? - gbn

2 Answers

0
votes

This happens because SQL Server does not really support nested transactions.

If you commit or rollback in a nested stored proc (not transaction), then you'll generate error 266 because of a @@TRANCOUNT mismatch on start and entry.

You should pair up your BEGIN TRAN and COMMITs in the same SPROC.

If there is no concept of nested transaction then you need to do rollback/commit in same sproc. You can use SET XACT_ABORT ON suppresses error 266 caused by mismatched @@TRANCOUNT.

-1
votes

not sure about nested transactions in sql server but you can try this one

Begin Try
 Begin Transaction1
  Call Proc1
   Call Proc2
   Call Proc3
 Commit
 End Transaction
Catch
 Rollback