SQL Server Transaction Nesting

Example code:

DECLARE @TranCounter INT;SET @TranCounter = @ @TRANCOUNT;IF @TranCounter > 0    --Procedure Called when there is    --An active transaction.    --Create a savepoint to be able    -- to roll back only the work done    --In the procedure if there are an    --error.    SAVE TRANSACTIONProceduresave;ELSE    --Procedure must start its own    --transaction.    BEGIN TRANSACTION;--Modify database.BEGINTRY/** Write your T-SQL here ... **/    --Get here if no errors; must commit    --Any transaction started in the    --procedure, but not commit a transaction    --started before the transaction was called.    IF @TranCounter = 0        --@TranCounter = 0 means no transaction was        --started before the procedure was called.        --The procedure must commit the transaction        --it started.        COMMIT TRANSACTION;ENDTRYBEGINCATCH--An error occurred; must determine    --which type of rollback would roll    -- back is only the    --procedure.    IF @TranCounter = 0        --Transaction started in procedure.        --Roll back to complete transaction.        ROLLBACK TRANSACTION; ELSE        --Transaction started before procedure        --called, don't roll back modifications        --made before the procedure was called.        IFXact_state ()<> -1            --If The transaction is still valid, just            --SavePoint set at the            --start of the stored procedure.            ROLLBACK TRANSACTIONProceduresave; --If The transaction is uncommitable, a            --rollback to the savepoint are not allowed            --because the savepoint rollback writes to            --The log. Just return to the caller, which            --Should roll back the outer transaction.    --After the appropriate rollback, echo error    --information to the caller.    DECLARE @ErrorMessage NVARCHAR(4000); DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; SELECT @ErrorMessage =error_message (); SELECT @ErrorSeverity =error_severity (); SELECT @ErrorState =error_state (); RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);ENDCatch

SQL Server Transaction Nesting