Persisting log in N-deep after rollback

Question

I'm facing a problem on one of my stored procedure.

I run a main procedure that call, in a transaction, a recursiveChildren one that call herself.

Something like this :

EXEC main
{
    EXEC log 'START'

    BEGIN TRANSACTION
        BEGIN TRY
            EXEC recursiveChildren
            {
                IF ...
                    EXEC recursiveChildren
                    {
                        IF ...
                            EXEC recursiveChildren
                            {
                                ...
                            }
                        END IF
                        EXEC log 'step X'
                    }
                END IF
                EXEC log 'step X'
            }

            IF ...
                COMMIT;
            ELSE
                THROW error;
        END TRY
        BEGIN CATCH
            ROLLBACK;
            EXEC log 'ROLLBACK'
        END CATCH
    END TRANSACTION

    EXEC log 'STOP'
}

As you can see, I call a logging procedure to keep track on what I'm doing, but if I need to use my THROW error, I'm losing all information about what happened.

I read TSQL logging inside transaction that talk about log and rollback, but the table variable is not fit for recursion as it's READONLY when used as a parameter of a procedure.

How can I keep my log whenever I rollback or not ?

If you have inserts like this inside a transaction and you roll back that transaction it rolls back EVERYTHING. The only exception would be if you used a table variable to hold your log data but since you are using recursion that is a challenge on its own. You would have to pass a table valued parameter over and over for this. What you would have to do is create a local instance of the same datatype on each iteration and fill it with the parameter passed in. Then pass the new local version when you recurse. It is ugly and a nightmare to maintain. — Sean Lange, Oct 24 '17 at 19:48
@SeanLange yes I'm pretty familiar with how transaction work, but the rollback is part of the logic, and the "why I rollback" is mandatory too :-/ ; So I don't have choice. Even with a recursive use of `table variable`, as it's on `readonly`, I can't get back my children log on the parent (technically, you can't use it to "extract" data from a procedure). — Blag, Oct 24 '17 at 20:04
If it is structured as in the question with the rollback in an error handler at the outer scope then you can use a temp table and just copy the contents to a table variable before doing the rollback. — Martin Smith, Oct 24 '17 at 20:15
As both Martin Smith I have eluded you will have to get this data into a table variable at some point prior to the rollback. — Sean Lange, Oct 24 '17 at 20:26
@MartinSmith This is exactly what I'm planning to do if I don't find any other way to do it. But if you recommend me that, I suppose there isn't any other way ? — Blag, Oct 24 '17 at 20:27
seems the simplest way. It may be possible to do it in other ways, e.g. with sp_trace_generateevent but that would then mean you’d also need to capture those. — Martin Smith, Oct 24 '17 at 20:30

score 0 · Accepted Answer · answered Nov 09 '17 at 23:53

As said by Martin, the solution in my case is to move the log into a var table before the update.

Here is the code if someone face the same problem :

CREATE PROCEDURE dbo.[main]
AS
BEGIN
    WHILE EXISTS(SELECT 1 FROM #tmp)
    BEGIN

        SET @last_id_log = (SELECT MAX([id]) FROM Logs);

        BEGIN TRANSACTION;
        BEGIN TRY

            SET @last_nb_error = @nb_error;

            EXEC dbo.[child] @nb_error output; -- recursive call of child

            IF( @nb_error - @last_nb_error = 0)
                COMMIT TRANSACTION;
            ELSE
                THROW 66666, 'Error while child, ROLLBACK', 1;

        END TRY
        BEGIN CATCH

            DECLARE @LogTable TABLE
                ([id] [int]
                ,[date] [datetime]
                ,[userName] [varchar](100)
                ,[error] [varchar](1000)
                );

            INSERT INTO @LogTable
            SELECT *
            FROM Logs
            WHERE [Id] > @last_id_log
            ;

            ROLLBACK TRANSACTION;

            INSERT INTO Logs
            SELECT *
            FROM @LogTable
            ;

            DELETE FROM @LogTable;
        END CATCH

    END
END

Persisting log in N-deep after rollback

1 Answers1