T-SQL stored procedure template with transaction handling
Reusable T-SQL stored procedure skeleton with nested transaction handling, savepoint and rollback logic in TRY/CATCH.
This skeleton is a stored procedure template that can be called on its own or from inside another transaction. It opens a transaction only when none is open. Otherwise it sets a savepoint, so the procedure's own work can be rolled back without ending the caller's transaction.
create procedure [usp_my_procedure_name]
as
begin
set nocount on;
declare @trancount int;
set @trancount = @@trancount;
begin try
if @trancount = 0
begin transaction
else
save transaction usp_my_procedure_name;
-- Do the actual work here
lbexit:
if @trancount = 0
commit;
end try
begin catch
declare @error int, @message varchar(4000), @xstate int;
select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
if @xstate = -1
rollback;
if @xstate = 1 and @trancount = 0
rollback
if @xstate = 1 and @trancount > 0
rollback transaction usp_my_procedure_name;
raiserror ('usp_my_procedure_name: %d: %s', 16, 1, @error, @message) ;
end catch
end
go
How it works
@@TRANCOUNTis saved on entry. If it is 0, the procedure starts a transaction and commits it at the end.- If a transaction is already open, the procedure creates a savepoint named after the procedure.
- In the
CATCHblock,XACT_STATE()selects the rollback:
| XACT_STATE() | Condition | Action |
|---|---|---|
| -1 | Transaction is uncommittable | ROLLBACK of the whole transaction |
| 1 | Committable, no outer transaction | ROLLBACK of the whole transaction |
| 1 | Committable, called inside a transaction | ROLLBACK TRANSACTION to the savepoint |
- The error number and message are re-raised with
RAISERROR.
Notes
- The
lbexitlabel is not referenced by the code; it is kept from the original template. - Replace
usp_my_procedure_namewith the name of the procedure.