Как работать с вложенными транзакциями процедур при вызове нескольких процедур?

Question

[t38c3j]

Появилась необходимость написать обертки для старых процедур которые будут вызывать сразу несколько процедур. В старых процедурах своя обработка ошибок и транзакций.
Как бы я не пробовал реализовать откат транзакции всегда ошибка если что-то пошло не так

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

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

sql

CREATE PROCEDURE dbo.Name
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    DECLARE @ErrorCode INT = 0;
    DECLARE @ErrorInfo NVARCHAR(2048) = '';

    BEGIN TRY
        EXEC @ErrorCode = dbo.Name1;
    END TRY
    BEGIN CATCH
        SET @ErrorInfo = ERROR_MESSAGE();
        THROW 60000, @ErrorInfo, 1;
    END CATCH;

    IF @ErrorCode <> 0
        BEGIN
            SET @ErrorInfo = @ErrorCode;
            THROW 60000, @ErrorInfo, 1;
        END;

    BEGIN TRY
        EXEC @ErrorCode = dbo.Name2;
    END TRY
    BEGIN CATCH
        -- Если произошла ошибка то надо откатить транзакцию первой  процедуры
        SET @ErrorInfo = ERROR_MESSAGE();
        THROW 60000, @ErrorInfo, 1;
    END CATCH;

    IF @ErrorCode <> 0
        BEGIN
            -- Если произошла ошибка то надо откатить транзакцию первой процедуры
            SET @ErrorInfo = @ErrorCode;
            THROW 60000, @ErrorInfo, 1;
        END;
    RETURN 0;
END;
GO

Comments

[Модератор]

t38c3j дорогой пользователь, настоятельно рекомендуем еще раз обратить самое пристальное внимание на п. 3.1 регламента работы сервиса (и, в особенности, на его последний абзац). В противном случае, ваши вопросы будут удаляться по причине тег-спама, а систематические нарушения приведут к блокировке учетной записи.

[t38c3j]

Модератор, Благодарю за предупреждение

[t38c3j]

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

[Korifa]

так а если добавить begin trun

BEGIN TRY
        begin trun
            EXEC @ErrorCode = dbo.Name1;
        commit trun
    END TRY
    BEGIN CATCH
        SET @ErrorInfo = ERROR_MESSAGE();
        print @ErrorInfo
        rollback trun
    END CATCH;

Answer 1

[d-stream]

Несколько коряво, но можно плясать от @@TRANCOUNT
Типа в catch проверять @@TRANCOUNT>0 и делать rollback , а при нормальном исполнении - аналогично коммит
Но можно наловить такие фейерические побочные эффекты... поэтому лучше по возможности в тех самых вложенных плясать от @@TRANCOUNT и начинать/коммитить/откатывать транзакции только на одном уровне вложенности

Answer 2

[Андрей Скоржинский]

Одно из решений описано в "Тестируем SQL Server код с tSQLt" раздел "Отмена/порча транзакций"