How to commit nested stored procedure, when general is raiserror

Question

I have one stored procedure proc_in which the insert data to tbl table

create table tbl(id int identity, val nvarchar(50))

create procedure proc_in
as
begin
    insert into tbl(val)
    values ('test')
end

and I have proc_out where I call proc_in

create procedure proc_out
as
begin
    exec proc_in

    DECLARE @MessageText NVARCHAR(100);
    SET @MessageText = N'This is a raiserror %s';
    RAISERROR(@MessageText, 16, 1, N'MSG')
end

How I can write proc_out that it return raiserror always to do insert in TBL table. I calling proc_out like this

begin tran 
    declare @err int = 0
    exec @err = proc_out
if @ERR = 0 
    commit tran 
else 
    rollback tran

Answer 1

You are wrapping your call in a single transaction in the calling context, therefore:

begin tran 
    declare @err int = 0
    exec @err = proc_out
if @ERR = 0 
    commit tran 
else 
    rollback tran

will always roll back everything that has happened within that transaction.

One way to avoid this is to move the transaction inside your 'proc_out' SP e.g.

create procedure proc_out
as
begin
    set nocount, xact_abort on;

    exec proc_in;

    begin tran;

    -- All your other code

    if @Err = 1 begin
        rollback;

        declare @MessageText nvarchar(100);
        set @MessageText = N'This is a raiserror %s';
        --raiserror(@MessageText, 16, 1, N'MSG');
        -- Actually for most cases now its recommended to use throw
        throw 51000, @MessageText 1; 
    end; else begin
        commit;
    end;

    return 0;
end;

Alternatively, and I haven't tried this, you could try using a savepoint e.g.

create procedure proc_out
as
begin
    set nocount on;

    exec proc_in;

    save transaction SavePoint1;

    declare @MessageText nvarchar(100);
    set @MessageText = N'This is a raiserror %s';
    raiserror(@MessageText, 16, 1, N'MSG');

    return 0;
end;

Then call it as:

begin tran;

declare @err int = 0;
exec @err = proc_out;

if @ERR = 0;
    commit tran;
end; else begin
    rollback tran SavePoint1;
    commit tran;
end;

I don't like this approach though, because knowledge of the inner workings of your SP has now leaked out to the calling context.

And some errors will roll back the entire transaction regardless.

Its important to be aware of the XACT_ABORT setting here.

When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing. Depending upon the severity of the error, the entire transaction may be rolled back even when SET XACT_ABORT is OFF. OFF is the default setting in a T-SQL statement, while ON is the default setting in a trigger.