LLI Wiki — My Digital Knowledge Base

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

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

Remarques

Mastodon LLIMastodon SocialFosstodon