Original
First I've created a SQL function that returns whether a transaction is required, basically it checks the @@TRANCOUNT, returning 1 if it is zero or returning 0 for another value.
CREATE FUNCTION dbo.TransactionRequired()
RETURNS BIT
AS
BEGIN
DECLARE @TransactionRequired BIT
DECLARE @TranCount INT
SELECT @TranCount = IsNull( @@TRANCOUNT, 0)
IF( @TranCount = 0)
SET @TransactionRequired = 1
ELSE
SET @TransactionRequired = 0
RETURN @TransactionRequired
END
GO
This is then used in stored procedures in the following pattern:
CREATE dbo.rflsp_TransactionExample
@RollBackTran BIT = 0 OUTPUT
AS
BEGIN
DECLARE @TransactionRequired BIT
SELECT @TransactionRequired = dbo.TransactionRequired() <-- (1)
-- Perform any prelim. non-transaction reliant processing here.
IF( @TransactionRequired = 1) BEGIN TRAN <--(2)
-- Perform transaction reliant processing here.
IF(###Fail Criteria Here###)
BEGIN
IF( @TransactionRequired = 1) <--(3)
ROLLBACK TRAN
ELSE
SET @RollBackTran = 1
RETURN
END
IF( @TransactionRequired = 1) COMMIT TRAN <--(4)
-- Perform any remaining non-transaction reliant processing here.
END
It is worth noting that the inclusion of the @RollBackTran in participating stored procedures is crucial as this allows non transactional stored procedures to pass the transaction state through when required.
To run through the steps:
1) This checks the current @@TRANCOUNT using the previous described function. The variable @TransactionRequired will only be 1 if @@TRANCOUNT was found to be zero (i.e. no transaction was currently working.
2) This makes sure a transaction is only started if one is required, i.e. we are not already in one.
3) If the code should fail then we only rollback the transaction if this was the procedure that started it, otherwise set the output parameter @RollBackTran, so the parent transaction knows that it needs to either roll back the transaction (if it started it) or in turn pass the value of @RollBackTran back to its parent.
4) Commit the transaction if this was the procedure that started it.
Using the above pattern in all stored procedures makes sure you never suffer the ROLLBACK TRAN does not have a corresponding BEGIN TRAN error messsage.