I have a stored procedure that needs to set a save point so that it can, under certain circumstances, undo everything it did and return an error code to the caller, or accept/commit it and return success to the caller. But I need it to work whether the caller has already started a transaction or not. The doc is extremely confusing on this subject. Here is what I think will work, but I’m not certain of all the ramifications.
The thing is - this
Stored Procedure (SP) is called by others. So I don’t know if they’ve started a transaction or not… Even if I require users to start a transaction to use my SP, I still have questions about the proper use of
Save Points …
My SP will test if a transaction is in progress, and if not, start one with
BEGIN TRANSACTION. If a transaction is already in progress, it will instead create a save point with
SAVE TRANSACTION MySavePointName, and save the fact this is what I did.
Then if I have to roll back my changes, if I did a
BEGIN TRANSACTION earlier, then I will
ROLLBACK TRANSACTION. If I did the save point, then I will
ROLLBACK TRANSACTION MySavePointName. This scenario seems to work great.
Here is where I get a little confused - if I want to keep the work I’ve done, if I started a transaction I will execute
COMMIT TRANSACTION. But if I created a save point? I tried
COMMIT TRANSACTION MySavePointName, but then the caller tries to commit its transaction and gets an error:
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
So I’m wondering then - a save point can be rolled back (that works:
ROLLBACK TRANSACTION MySavePointName will NOT roll back the caller’s transaction). But perhaps one never needs to “commit” it? It just stays there, in case you need to roll back to it, but goes away once the original transaction is committed (or rolled back)?
If there is a “better” way to “nest” a transaction, please shed some light as well. I haven’t figured out how to nest with
BEGIN TRANSACTION but only rollback or commit my internal transaction. Seems
ROLLBACK will always roll back to the top transaction, while
COMMIT simply decrements
I believe I’ve figured this all out now, so I will answer my own question…
I’ve even blogged my findings if you want more details at http://geekswithblogs.net/bbiales/archive/2012/03/15/how-to-nest-transactions-nicely—quotbegin-transactionquot-vs-quotsave.aspx
So my SP starts with something like this, to start a new transaction if there is none, but use a Save Point if one is already in progress:
DECLARE @startingTranCount int
Then, when ready to commit the changes, you only need to commit if we started the transaction ourselves:
IF @startingTranCount = 0
And finally, to roll back just your changes so far:
-- Roll back changes...
Article link: http://xnerv.wang/save-transaction-vs-begin-transaction-sql-server-how-to-nest-transactions-nicely/
Reprinted from: (StackOverflow) SAVE TRANSACTION vs BEGIN TRANSACTION (SQL Server) how to nest transactions nicely