Executing a proc which saves tran in a transaction got error of "mismatching number of BEGIN and COMMIT"?

Question

I created the following stored procedure.

alter proc test as if @@trancount = 0 begin begin tran test; end; else begin save tran test; end; -- ...... if @@trancount > 0 begin print formatmessage('trancount %d', @@trancount); commit tran test; print formatmessage('commit %d', @@trancount); end;

It works when exec dbo.test. However, the following code got the following error?

begin tran; exec dbo.test; rollback; -- Or commit

Msg 266, Level 16, State 2, Procedure test, Line 19

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

Msg 3902, Level 16, State 1, Line 22

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

This might be helpful: If @@TRANCOUNT has a different value when a stored procedure finishes than it had when the procedure was executed, an informational error (266) occurs. Likewise, since stored procedures are technically batches in themselves, and you're skipping the part where you begin the transaction, it will throw an error for that as well. — ZLK
– ZLK, Commented Jan 23, 2018 at 23:01

David Browne - Microsoft · Accepted Answer · 2018-01-23 23:01:51Z

2

SAVE TRAN does not create a nested transaction, so when you COMMIT TRAN you are commiting the transaction that was started outside the proc. And that is not allowed, producting that error.

If you want to SAVE TRAN in the proc, then you don't commit, you just ROLLBACK to the savepoint on error.

answered Jan 23, 2018 at 23:01

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

ca9163d9 Over a year ago

Got it. I think what I need is actually nested transaction.

CodingYoshi Over a year ago

SAVE TRAN does not create a nested transaction, so when you COMMIT TRAN you are commiting the transaction that was started outside the proc. Ok so then why does the error say The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.--Why does it not commit the one started outside?

David Browne - Microsoft Over a year ago

Why does it not commit the one started outside? It does, and you're not allowed to do that, thus the error.

ca9163d9 Over a year ago

@CodingYoshi, I will need to get rid of save tran

ca9163d9 Over a year ago

@DavidBrowne-Microsoft, I get rid of save tran but now it got a strange issue on rollback only. A new question has been posted stackoverflow.com/questions/48412586/…

ca9163d9 · Accepted Answer · 2018-01-24 01:01:52Z

The following code shows a way to create a proc which can be commit/rollback its transactions and be executed in or not in another transaction.

alter proc test @rollback bit = 1 as declare @trancount int = @@trancount; if @trancount = 0 begin begin tran test; end; else begin save tran test; end; -- ...... if @@trancount > 0 begin if @trancount = 0 begin print 'no external tran'; if @rollback = 1 rollback; else commit; end; else begin print formatmessage('trancount %d', @@trancount); if @rollback = 0 begin -- commit tran test; print 'just return'; end; else begin rollback tran test; print 'rollback'; end; print formatmessage('after %d', @@trancount); end; end; go

Collectives™ on Stack Overflow

Executing a proc which saves tran in a transaction got error of "mismatching number of BEGIN and COMMIT"?

2 Answers 2

5 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Related