SQL Server XACT_ABORT с исключением

У меня есть большая хранимая процедура, которая использует несколько блоков TRY / CATCH, чтобы отлавливать и регистрировать отдельные ошибки. Я также обернул транзакцию вокруг всего содержимого процедуры, чтобы иметь возможность откатить всю вещь в случае ошибки, возникшей где-то по пути (чтобы предотвратить много грязной очистки); XACT_ABORT был включен, поскольку в противном случае он не откатил бы всю транзакцию.

Ключевой компонент:
В моей базе данных есть таблица, в которую вставляется запись при каждом запуске этой процедуры с результатами операций и подробностями о том, что пошло не так.

Происходит забавная вещь - на самом деле, когда я наконец выяснил, что было не так, это было довольно очевидно ... оператор вставки в мою таблицу журналов также откатывается, следовательно, если я не запускаю это из SSMS, Я не смогу увидеть, что это даже было выполнено, так как откат удаляет все трансы активности.

Вопрос:
Можно ли было бы откатить всю транзакцию за исключением этого единственного оператора вставки? Я все еще хотел бы сохранить сообщение об ошибке, которое я компилирую во время выполнения хранимой процедуры.

Спасибо!

~ Eli

Обновление 6/28
Вот пример кода того, на что я смотрю. Ключевое различие между этим и примерами, представленными @Alex и @gameiswar, состоит в том, что в моем случае блоки try / catch все вложены в одну транзакцию. Цель этого состоит в том, чтобы иметь несколько уловов (для нескольких таблиц), хотя мы бы откатили весь беспорядок, даже если последнее обновление не удалось.

SET XACT_ABORT ON;  
BEGIN TRANSACTION  
    DECLARE @message AS VARCHAR(MAX) = '';  

    -- TABLE 1
    BEGIN TRY
        UPDATE TABLE xx 
        SET yy = zz
    END TRY
    BEGIN CATCH
        SET @message = 'TABLE 1 '+ ERROR_MESSAGE();

        INSERT INTO LOGTABLE
        SELECT 
            GETDATE(),
            @message
        RETURN;
    END CATCH

    -- TABLE 2
    BEGIN TRY
        UPDATE TABLE sss 
        SET tt = xyz
    END TRY
    BEGIN CATCH
        SET @message = 'TABLE 2 '+ ERROR_MESSAGE();

        INSERT INTO LOGTABLE
        SELECT 
            GETDATE(),
            @message
        RETURN;
    END CATCH
COMMIT TRANSACTION
 TheGameiswar27 июн. 2016 г., 21:37
Когда XaCT_ABORT установлен в on. Вы не можете исключить один оператор для успеха.
 Eli27 июн. 2016 г., 22:42
@ TheGameiswar есть работа?
 TheGameiswar28 июн. 2016 г., 05:20
Смотрите мой ответ обновлен

Ответы на вопрос(3)

Я не знаю деталей, но ИМХО общая логика может быть такой.

--set XACT_ABORT ON --not include it
declare @result varchar(max) --collect details in case you need it
begin transaction
begin try
--your logic here
--if something wrong RAISERROR([email protected])
--everything OK
commit
end try
begin catch
--collect error_message() and other into @result
rollback
end catch
insert log(result) values (@result)
 Eli28 июн. 2016 г., 15:47
Алекс не уверен, что вы имеете в виду о промежуточных блоках try / catch ... исходя из потребностей этого sproc, нам нужно обновить серию из примерно 10 таблиц, и вы бы хотели отловить каждую из них отдельно, чтобы узнать, какая из них взорвали и какие параметры были и т.д. Вы, кажется, использовали ту же теорию, что и @TheGameiswar, в которой вы сохраняете ошибку в переменной, которая выдержит откат. Я поиграю с этим и посмотрю, что из этого получится. Я очень ценю ваш вклад!
 Alex Kudryashev27 июн. 2016 г., 23:46
Поэтому мы думаем синхронно. Подумайте дважды о юзабилити промежуточного try / catch.
 Eli27 июн. 2016 г., 23:29
Ваш фрагмент очень напоминает мою структуру кода, хотя есть ряд таких блоков try / catch. У меня есть сообщение об ошибке, полученное с помощью error_message (), которое добавляется в короткую заметку, сообщающую мне некоторые подробности о том, что было предпринято оператором ... это сообщение затем вставляется в таблицу разрешений. Разница в том, что оператор вставки происходит внутри блока catch. Он записывает сообщение в таблицу, хотя после отката (о котором я не упоминаю явно) эта запись также теряется.

Вы можете попробовать что-то вроде ниже, что гарантирует, что вы регистрируете операцию. Это использует тот факт, что табличные переменные не откатываются.

Код Psuedo только для того, чтобы дать вам представление:

create table test1
(
id int primary key
)

create table logg
(
errmsg varchar(max)
)



declare @errmsg varchar(max)

set xact_abort on
begin try
begin tran
insert into test1
select 1

insert into test1
select 1

commit
end try

begin catch
set @errmsg=ERROR_MESSAGE()
select @errmsg as "in block"
if @@trancount>0
rollback tran

end catch
set xact_abort off


select @errmsg as "after block";

insert into logg
select @errmsg


select * from logg
 Eli28 июн. 2016 г., 21:33
TheGameiswar, @Alex Пожалуйста, проверьте мое обновление выше, которое включает в себя фрагмент кода
 Eli28 июн. 2016 г., 15:59
Подход, который вы использовали, похож на тот, который опубликован @Alex выше, хотя вы более четко объяснили, почему использовать переменную. У вас есть большая часть того, что я ищу, за исключением того факта, что у меня есть серия блоков try / catch, в которых я хотел бы остановить обработку, если один из них войдет в блок catch. Поиграю с твоей идеей и опубликую то, что найду. Спасибо!
Решение Вопроса

Хорошо ... Я смог решить эту проблему, используя комбинацию замечательных предложений, выдвинутых Алексом и GameisWar, с добавлением оператора потока управления T-SQL GOTO.

Основная идея заключалась в том, чтобы сохранить сообщение об ошибке в переменной, которая выдержит откат, а затем Catch отправит вас на метку FAILURE, которая будет выполнять следующие действия:

Откат транзакцииВставьте запись в таблицу журнала, используя данные из вышеупомянутой переменнойВыход из хранимой процедуры

Я также использую второй оператор GOTO, чтобы убедиться, что при успешном запуске пропустится раздел FAILURE и будет зафиксирована транзакция.

Ниже приведен фрагмент кода того, как выглядел тестовый SQL. Это сработало как прелесть, и я уже реализовал это и успешно протестировал его в нашей производственной среде.

Я действительно ценю всю помощь и вклад!

SET XACT_ABORT ON               
DECLARE @MESSAGE VARCHAR(MAX) = '';

BEGIN TRANSACTION 
    BEGIN TRY
        INSERT INTO TEST_TABLE VALUES ('TEST');     -- WORKS FINE
    END TRY 
    BEGIN CATCH     
        SET @MESSAGE = 'ERROR - SECTION 1: ' + ERROR_MESSAGE();
        GOTO FAILURE;
    END CATCH

    BEGIN TRY
        INSERT INTO TEST_TABLE VALUES ('TEST2');        --WORKS FINE
        INSERT INTO TEST_TABLE VALUES ('ANOTHER TEST'); -- ERRORS OUT, DATA WOULD BE TRUNCATED
    END TRY 
    BEGIN CATCH 
        SET @MESSAGE = 'ERROR - SECTION 2: ' + ERROR_MESSAGE();
        GOTO FAILURE;
    END CATCH

GOTO SUCCESS;

FAILURE:        
    ROLLBACK
    INSERT INTO LOGG SELECT @MESSAGE
    RETURN; 

SUCCESS:
COMMIT TRANSACTION

Ваш ответ на вопрос