Thread: MS SQL Server General Questions/Transactions

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()
    DECLARE @TransactionRequired BIT
    DECLARE @TranCount INT
    SELECT @TranCount = IsNull( @@TRANCOUNT, 0)
    IF( @TranCount = 0)
        SET @TransactionRequired = 1
        SET @TransactionRequired = 0
    RETURN @TransactionRequired
This is then used in stored procedures in the following pattern:

CREATE dbo.rflsp_TransactionExample
    @RollBackTran BIT = 0 OUTPUT
    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###)
        IF( @TransactionRequired = 1)                                     <--(3)
            ROLLBACK TRAN
            SET @RollBackTran = 1

    IF( @TransactionRequired = 1) COMMIT TRAN                             <--(4)

    -- Perform any remaining non-transaction reliant processing here.

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.