TSQL logging inside transaction

Question

I'm trying to write to a log file inside a transaction so that the log survives even if the transaction is rolled back.

--start code

begin tran

insert [something] into dbo.logtable

[[main code here]]

rollback

commit

-- end code

You could say just do the log before the transaction starts but that is not as easy because the transaction starts before this S-Proc is run (i.e. the code is part of a bigger transaction)

So, in short, is there a way to write a special statement inside a transaction that is not part of the transaction. I hope my question makes sense.

Answer 1

Use a table variable (@temp) to hold the log info. Table variables survive a transaction rollback.

See this article.

Answer 2

I do this one of two ways, depending on my needs at the time. Both involve using a variable, which retain their value following a rollback.

1) Create a DECLARE @Log varchar(max) value and use this: @SET @Log=ISNULL(@Log+'; ','')+'Your new log info here'. Keep appending to this as you go through the transaction. I'll insert this into the log after the commit or the rollback as necessary. I'll usually only insert the @Log value into the real log table when there is an error (in theCATCH` block) or If I'm trying to debug a problem.

2) create a DECLARE @LogTable table (RowID int identity(1,1) primary key, RowValue varchar(5000). I insert into this as you progress through your transaction. I like using the OUTPUT clause to insert the actual IDs (and other columns with messages, like 'DELETE item 1234') of rows used in the transaction into this table with. I will insert this table into the actual log table after the commit or the rollback as necessary.