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