I have a stored procedure that only executes 3 stored procedures inside them. I am only using 1 parameter to store if the master SP is successful.
If the first stored procedure works fine in the master stored procedure, but the 2nd stored procedure fails, then will it automatically roll back all the SP's in the master SP or do I have to make some command?
Here is my procedure:
CREATE PROCEDURE [dbo].[spSavesomename]
-- Add the parameters for the stored procedure here
@successful bit = null output
AS
BEGIN
begin transaction createSavebillinginvoice
begin Try
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
EXEC [dbo].[spNewBilling1]
END
BEGIN
EXEC [dbo].[spNewBilling2]
END
BEGIN
EXEC [dbo].[spNewBilling3]
END
set @successful = 1
end Try
begin Catch
rollback transaction createSavesomename
insert into dbo.tblErrorMessage(spName, errorMessage, systemDate)
values ('spSavesomename', ERROR_MESSAGE(), getdate())
return
end Catch
commit transaction createSavesomename
return
END
GO
Best Answer
Given only the code shown in the question, and assuming that none of the three sub-procs have any explicit transaction handling, then yes, an error in any of the three sub-procs will be caught and the
ROLLBACK
in theCATCH
block will roll back all of the work.BUT here are some things to note about transactions (at least in SQL Server):
There is only ever one real transaction (the first one), no matter how many times you call
BEGIN TRAN
BEGIN TRAN
, whether or not it is named, the transaction counter is incremented by 1.SELECT @@TRANCOUNT;
COMMIT
commands issued when@@TRANCOUNT
is at 2 or above do nothing more than reduce, one at a time, the transaction counter.COMMIT
is issued when the@@TRANCOUNT
is at1
Save points allow for creating a subset of work within the transaction that can be undone.
SAVE TRAN {save_point_name}
commandROLLBACK {save_point_name}
. (more on this below)SAVE TRAN {save_point_name}
, including any save points created after the one being rolled-back was created (hence the "nesting").SAVE TRAN
cannot be undone except by issuing a fullROLLBACK
of the entire transaction.COMMIT
when@@TRANCOUNT
is at 2 or above, has no effect on save points (because again, transaction levels above 1 don't exist outside of that counter).You cannot commit specific named transactions. The transaction "name", if provided along with the
COMMIT
, is ignored and only exists for readability.A
ROLLBACK
issued without a name will always rollback ALL transactions.A
ROLLBACK
issued with a name must correspond to either:Assuming no
SAVE TRAN
has been called with the same transaction name, this will rollback ALL transactions.This behavior will "undo" all changed made since the most recent
SAVE TRAN {save_point_name}
was called.SAVE TRAN
commands issued with its name, then each ROLLBACK of that transaction name will undo each save point until there are none left of that name. After that, a ROLLBACK issued of that name will rollback ALL transactions.For example, assume the following commands were run in the order shown:
Now, if you issue (each of the following scenarios is independent of each other):
ROLLBACK TRAN B
once: It will undo "DML Query 4".@@TRANCOUNT
is still 2.ROLLBACK TRAN B
twice: It will undo "DML Query 4" and then error as there is no corresponding save point for "B".@@TRANCOUNT
is still 2.ROLLBACK TRAN A
once: It will undo "DML Query 4" and "DML Query 3".@@TRANCOUNT
is still 2.ROLLBACK TRAN A
twice: It will undo "DML Query 4", "DML Query 3", and "DML Query 2".@@TRANCOUNT
is still 2.ROLLBACK TRAN A
thrice: It will undo "DML Query 4", "DML Query 3", and "DML Query 2". Then it will rollback the entire transaction (all that was left was "DML Query 1").@@TRANCOUNT
is now 0.COMMIT
once:@@TRANCOUNT
goes down to 1.COMMIT
once and thenROLLBACK TRAN B
once:@@TRANCOUNT
goes down to 1. Then it will undo "DML Query 4" (proving that COMMIT didn't do anything) .@@TRANCOUNT
is still 1.Transaction names and save point names:
A stored procedure is not, in itself, an implicit transaction. Each query if no explicit transaction has been started, is an implicit transaction. This is why explicit transactions around single queries are not necessary unless there can be a programmatic reason to do a
ROLLBACK
, else any error in the query is an automatic rollback of that query.When calling a stored procedure, it must exit with the value of
@@TRANCOUNT
being the same as when it was called. Meaning, you cannot:BEGIN TRAN
in the proc without committing it, expecting to commit in the calling/parent process.ROLLBACK
if an explicit transaction was started prior to the proc being called as it will return@@TRANCOUNT
to 0.If you exit a stored procedure with a transaction count that is either higher or lower than when it stared, you will get an error similar to:
Table Variables, just like regular variables, are not bound by transactions.
Regarding having transaction handling in procs that can either be called independently (and hence need transaction handling) or call from other procs (hence not needing transaction handling): this can be accomplished in a couple different ways.
The way that I have been handling it for several years now that seems to work well is to only
BEGIN
/COMMIT
/ROLLBACK
at the outer-most layer. Sub-proc calls just skip the transaction commands. I have outlined below what I put into each proc (well, each one that needs transaction handling).DECLARE @InNestedTransaction BIT;
In place of simple
BEGIN TRAN
, do:In place of simple
COMMIT
, do:In place of simple
ROLLBACK
, do:This method should work the same regardless of whether the transaction was started within SQL Server or if it was started at the app layer.
For the full template of this Transaction handling within the
TRY...CATCH
construct, please see my answer to the following DBA.SE question: Are we required to handle Transaction in C# Code as well as in stored procedure.Moving beyond the "basics", there are some additional nuances of transactions to be aware of:
By default, Transactions are, most of the time, not automatically rolled-back / cancelled when an error occurs. This is usually not a problem as long as you have proper error handling and call
ROLLBACK
yourself. However, sometimes things get complicated, such as in the case of batch-aborting errors, or when usingOPENQUERY
(or Linked Servers in general) and an error occurs on the remote system. While most errors can be trapped usingTRY...CATCH
, there are two that cannot be trapped that way (can't remember which ones at the moment, though--researching). In these cases, you must useSET XACT_ABORT ON
to properly rollback the Transaction.SET XACT_ABORT ON causes SQL Server to immediately roll-back any Transaction (if one is active) and abort the batch if any error occurs. This setting existed prior to SQL Server 2005, which introduced the
TRY...CATCH
construct. For the most part,TRY...CATCH
handles most situations and so mostly obsoletes the need forXACT_ABORT ON
. However, when usingOPENQUERY
(and possibly one other scenario that I can't remember at the moment), then you will still need to useSET XACT_ABORT ON;
.Inside of a Trigger,
XACT_ABORT
is implicitly set toON
. This causes any error within the Trigger to cancel the entire DML statement that fired the Trigger.You should always have proper error handling, especially when using Transactions. The
TRY...CATCH
construct, introduced in SQL Server 2005, provides a means of handling nearly all situations, a welcome improvement over testing for@@ERROR
after each statement, which didn't help much with batch-aborting errors.TRY...CATCH
introduced a new "state", however. When not using theTRY...CATCH
construct, if you have an active Transaction and an error occurs, then there are several paths that can be taken:XACT_ABORT OFF
and statement-aborting error: Transaction is still active and processing continues with the next statement, if any.XACT_ABORT OFF
and most batch-aborting errors: Transaction is still active and processing continues with the next batch, if any.XACT_ABORT OFF
and certain batch-aborting errors: Transaction is rolled-back and processing continues with the next batch, if any.XACT_ABORT ON
and any error: Transaction is rolled-back and processing continues with the next batch, if any.HOWEVER, when using
TRY...CATCH
, batch-aborting errors do not abort the batch, but instead transfer control to theCATCH
block. WhenXACT_ABORT
isOFF
, the Transaction will still be active the vast majority of the time, and you will need toCOMMIT
, or most likely,ROLLBACK
. But when encountering certain batch-aborting errors (such as withOPENQUERY
), or whenXACT_ABORT
isON
, the Transaction will be in a new state, "uncommitable". In this state you cannotCOMMIT
, nor can you do any DML operations. All you can do isROLLBACK
andSELECT
statements. However, in this "uncomittable" state, the Transaction was rolled-back upon the error occurring, and issuing theROLLBACK
is just a formality, but one that must be done.A function, XACT_STATE, can be used to determine if a Transaction is active, uncommitable, or doesn't exist. It is recommended (by some, at least) to check this function in the
CATCH
block to determine if the result is-1
(i.e. uncommitable) instead of testing if@@TRANCOUNT > 0
. But withXACT_ABORT ON
, that should be the only possible state to be in, so it seems that testing for@@TRANCOUNT > 0
andXACT_STATE() <> 0
are equivalent. On the other hand, whenXACT_ABORT
isOFF
and there is an active Transaction, then it is possible to have a state of either1
or-1
in theCATCH
block, which allows for the possibility of issuingCOMMIT
instead ofROLLBACK
(although, I cannot think of a case for when someone would want toCOMMIT
if the Transaction is commitable). More information and research on usingXACT_STATE()
within aCATCH
block withXACT_ABORT ON
can be found in my answer to the following DBA.SE question: In what cases a transaction can be committed from inside the CATCH block when XACT_ABORT is set to ON?. Please note that there is a minor bug withXACT_STATE()
that causes it to falsely return1
in certain scenarios: XACT_STATE() returns 1 when used in SELECT with some system variables but without FROM clauseNotes about the original code:
BEGIN
andEND
around eachEXEC
call