Как зафиксировать вложенную хранимую процедуру, когда вообще есть рейзеррор

У меня есть одна хранимая процедура proc_in, которая вставляет данные в таблицу tbl

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

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

и у меня есть proc_out, куда я звоню 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

Как я могу написать proc_out, чтобы он всегда возвращал raiserror для вставки в таблицу TBL. Я звоню proc_out вот так

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

sql sql-server stored-procedures tsql raiserror
person Tigran Shahnazaryan    schedule 03.02.2021    source источник
comment
Я все равно хочу вставить tbl, когда proc_out имеет рейзеррор, и моя транзакция будет откатана.   -  person Tigran Shahnazaryan    schedule 04.02.2021
comment
proc_out у меня большая процедура и она иногда выдает ошибку и делать откат, а proc_in работает на мой сервис и сохраняет логи, я все равно хочу сохранить свои логи.   -  person Tigran Shahnazaryan    schedule 04.02.2021

Ответы (1)


arrow_upward
0
arrow_downward

Вы оборачиваете свой вызов в одну транзакцию в контексте вызова, поэтому:

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

всегда будет откатывать все, что произошло в этой транзакции.

Один из способов избежать этого - переместить транзакцию внутри вашего SP 'proc_out', например.

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;

В качестве альтернативы, и я этого не пробовал, вы можете попробовать использовать savepoint, например.

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;

Затем назовите это как:

begin tran;

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

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

Однако мне не нравится такой подход, потому что информация о внутренней работе вашего SP теперь просочилась в вызывающий контекст.

И некоторые ошибки независимо откатят всю транзакцию.

Важно знать о XACT_ABORT.

Когда SET XACT_ABORT имеет значение OFF, в некоторых случаях выполняется откат только инструкции Transact-SQL, вызвавшей ошибку, и транзакция продолжает обработку. В зависимости от серьезности ошибки вся транзакция может быть отменена, даже если SET XACT_ABORT выключен. OFF — это параметр по умолчанию в инструкции T-SQL, а ON — параметр по умолчанию в триггере.

person Dale K    schedule 03.02.2021
comment
спасибо последний вариант работает - person Tigran Shahnazaryan; 04.02.2021