Modèle de procédure stockée T-SQL avec gestion des transactions
Squelette de procédure stockée T-SQL réutilisable : transaction imbriquée, point de sauvegarde et annulation dans TRY/CATCH.
Ce squelette est un modèle de procédure stockée qui peut être appelée seule ou depuis une autre transaction. Il ouvre une transaction uniquement si aucune n'est en cours. Sinon, il pose un point de sauvegarde : le travail de la procédure peut alors être annulé sans terminer la transaction de l'appelant.
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
Fonctionnement
@@TRANCOUNTest sauvegardé à l'entrée. S'il vaut 0, la procédure démarre une transaction et la valide à la fin.- Si une transaction est déjà ouverte, la procédure crée un point de sauvegarde portant le nom de la procédure.
- Dans le bloc
CATCH,XACT_STATE()détermine l'annulation :
| XACT_STATE() | Condition | Action |
|---|---|---|
| -1 | La transaction ne peut plus être validée | ROLLBACK de la transaction entière |
| 1 | Validable, sans transaction englobante | ROLLBACK de la transaction entière |
| 1 | Validable, appelée dans une transaction | ROLLBACK TRANSACTION jusqu'au point de sauvegarde |
- Le numéro et le message d'erreur sont relancés avec
RAISERROR.
Remarques
- Le label
lbexitn'est pas référencé par le code ; il est conservé depuis le modèle d'origine. - Remplacer
usp_my_procedure_namepar le nom de la procédure.