11> CREATE PROC usp_MyProc
12> AS
13> DECLARE
14> @TranCount int
15> SET
16> @TranCount = @@TRANCOUNT
17> IF @TranCount > 0
18> SAVE TRAN usp_MyProc -- No existing transaction
19> ELSE
20> BEGIN TRAN usp_MyProc -- Transaction in progress
21> --do work here ...
22> IF @@ERROR > 0
23> BEGIN -- Failure
24> RAISERROR ('usp_MyProc - Bailing out. ', 16, 1)
25> ROLLBACK TRAN usp_MyProc
26> RETURN
27> END
28> ELSE IF @Trancount = 0 -- Started our own transaction
29> COMMIT TRAN usp_MyProc -- Success
30> GO
1>
2> drop procedure usp_MyProc
3> GO