The Single-Level Model
In the single-level model, if a transaction is already in place, the procedure will not start a new one; instead, the transaction level remains unchanged (i.e., at a single level). If it has to roll back and it did not start the transaction, the procedure raises an error and returns an error message to the caller. In this model, the procedures do not take the transaction level beyond 1.
The basic strategy for the single-level model is to start by declaring a local variable to record whether this procedure should begin a transaction or not. If the transaction count is 0 when the transaction starts, the procedure issues a BEGIN TRANSACTION.
If you call another stored procedure, you should capture both the return value of the stored procedure and the value of @@ERROR. If the return value from the called procedure is -1, or if @@ERROR was greater than 0, the procedure assumes that an error has occurred that requires us to stop further processing and exit through an error path. If the return value from the called procedure is -1, the called procedure has already raised an error so there's no need to raise one again. However, if the stored procedure call failed, or there was a non-trappable error in the called procedure, you should raise an error and report it to the caller so that you'll know what procedure actually failed.
|A stored procedure transaction should be rolled back at the same level at which it was started, so only the calling procedure that starts a transaction should ever roll back.|
If the stored procedure has its own critical event, such as an INSERT
, or DELETE, then you should test that command. Again, capture the value of @@ERROR; if it is greater than zero, the procedure should abort its processing. In this case you should raise an error indicating where the problem occurred, and exit through the error path.
In the procedure's error exit path, you test whether this procedure began a transaction. If it did, then the procedure issues a ROLLBACK
, In either case the procedure should RETURN
a -1 to tell a calling procedure that it should also exit through its error exit path.
When no errors occur, the procedure should exit through a normal exit path; if it started a transaction, the procedure should COMMIT
and return a 0. If it did not start a transaction, there's no need to issue a COMMIT
contains the outline of a stored procedure using the single-level model. It shows the code for the outermost procedure, but the same code works at any level. If a procedure is at the innermost level of a set of nested procedures, you can remove the code that traps for calling a stored procedure.
If a procedure does not begin a transaction, set the @LocalTran
flag to 0. The procedure will never execute either a COMMIT
or a ROLLBACK
, but will still trap for errors calling other stored procedures and exit out its error path (the ErrExit
label) if it detects a failure from called procedures.
I've used this model in production a couple times now, and it works well. When SQL Server returns errors from low in the procedure nesting, the error messages help to easily pinpoint the location.
The Multi-Level Model
SQL Server MVP Fernando Guerrero pointed out to me that there is an alternative approach that does
permit transaction nesting. The overall algorithm is very similar. In the multi-level model, a procedure may begin a new transaction; but if it detects the need to roll back and the @@TRANSACTION
value is greater than 1, it raises an error, returns an error message to the caller, and issues a COMMIT
instead of a ROLLBACK
. The multi-level model allows transaction levels to increase.
Both models only roll back a transaction at the outermost level. While the multi-level model explicitly begins a transaction, it makes sure that every procedure below the outermost one issues a COMMIT
rather than a ROLLBACK
, so the @@TRANCOUNT
level is properly decremented even if the procedures are all exiting through their error path.
In this model, you do not need to keep track of whether this procedure began a transaction. Instead, just issue the BEGIN TRANSACTION
. The error handling for calling other stored procedures and issuing critical commands remains the same.
If the procedure exits via its normal exit path, it should just issue a COMMIT
and return a 0. But if the procedure exits out its error path (through its ErrExit
label), there are two options. If the value of @@TRANCOUNT
is greater than 1, this procedure did not initiate the transaction, so it should just COMMIT
and return a -1. If @@TRANCOUNT
is exactly 1, this procedure did initiate the transaction, so it issues a ROLLBACK
and returns -1.
shows sample code using this strategy.
Again, if you are not calling other procedures, you can just remove the related code. However, if you are not using a transaction in this procedure, you'll also need to remove the COMMIT
conditions from the code.
Comparing the Two Models
What's interesting about both models is that they can inter-operate. Both follow the rule that they will not roll back a transaction if they did not initiate it, and they both always leave the transaction level of a stored procedure the same as when they entered the transaction.
The advantage of the multi-level approach is that you can use code that is somewhat simpler. The advantage of the single-level approach is that you can easily turn the transaction handling on or off without removing or commenting out lines of code.
I've used the single-level model in two applications and it is working fine. Whichever model you choose, you'll have a solid approach to handling Transact-SQL errors in nested procedures that use transactions.