How to deal with nested transaction procedures when calling multiple procedures?

Question

Transact SQL

There was a need to write wrappers for old procedures that will call several procedures at once. The old procedures have their own handling of errors and transactions.
No matter how I try to rollback a transaction, there is always an error if something goes wrong

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

I've tried both named transactions and state persistence, but most likely it's my lack of experience with MSSQL and its philosophy about transactions.
How to correctly implement the work with transactions so that if an error occurs as a result of working with subsequent procedures, then roll back all transactions of previous procedures?

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

Answer 1

Somewhat clumsily, but you can dance from @@TRANCOUNT
Type in catch check @@TRANCOUNT>0 and do rollback , and in normal execution - similar to a commit
But you can catch such enchanting side effects ... so it's better, if possible, to dance from @@TRANCOUNT and start/commit/roll back transactions only at one nesting level

Answer 2

One solution is described in the " Testing SQL Server Code with tSQLt " section "Reversing/Corrupting Transactions"

Answer 3

Theoretically, there should be a case sensitivity problem, but I can't figure out the script from this shit code anyway.
In general, you can guess for a long time. Read here phpfaq.ru/debug set the maximum level of error display, debug.