CODEWITHSUNDEEP
sql-servert-sqltransactions
Angelo Badellino
Angelo Badellino

Reputation: 2201

T-SQL EXEC command inside transaction

I need to execute a store procedure from another with the common EXEC command. I need to be sure, that all the sql statements will be under transaction.

BEGIN TRANSACTION
BEGIN TRY   

    SET @Esercizio = (SELECT ESERCIZIO_OBIETTIVI_CONSUNTIVARE from TB_SCHEDE WHERE MATRICOLA = @iMATRICOLA and COD_VALUTAZIONE = @iCOD_VALUTAZIONE)
    SET @TipoProcesso = (SELECT ISNULL(TipoProcesso, 'middle') from TB_SCHEDE WHERE MATRICOLA = @iMATRICOLA and COD_VALUTAZIONE = @iCOD_VALUTAZIONE)

    DELETE FROM TB_SCHEDE WHERE MATRICOLA = @iMATRICOLA and COD_VALUTAZIONE = @iCOD_VALUTAZIONE
    DELETE FROM TB_SCHEDE_AUTOVAL WHERE MATRICOLA = @iMATRICOLA and COD_VALUTAZIONE = @iCOD_VALUTAZIONE
    DELETE FROM TB_OBIETTIVI WHERE MATRICOLA = @iMATRICOLA and ESERCIZIO =  @Esercizio
    DELETE FROM TB_OBIETTIVI_AUTOVAL WHERE MATRICOLA = @iMATRICOLA and ESERCIZIO =  @Esercizio

    EXEC AnotherStore @iCOD_VALUTAZIONE, @iMATRICOLA, @TipoProcesso
    COMMIT TRANSACTION

END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

If the AnotherStore procedure throw an exception, does the DB engine ensure rollback from the caller store procedure?

Hope to be clear.

Upvotes: 7

Views: 11285

Answers (2)

user1166147
user1166147

Reputation: 1610

The simple answer is yes it will rollback the changes for the caller stored procedure, but think it through if you have transactions inside the other stored procedure, there are possibilities for things not behaving as you expect if that is the case. A ROLLBACK will affect ALL of the transactions, although this is probably what you want. You can use @@TRANCOUNT within your catch and determine if you want to rollback the entire thing, and Savepoints.

Everything you do to the database between the BEGIN TRANSACTION and COMMITor ROLLBACK is part of the transaction, and if any lines get an error, control will be routed to the CATCH block where the transaction will be rolled back. Things like table variables would fall outside of this scope and not get rolled back. And as @David Brabant said, BEGIN TRANSACTION should be in the BEGIN TRY block.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294387

See Exception handling and nested transactions for an example of execption handling in the presence of transactions:

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

Upvotes: 2

Related Questions