SQL Server – Using table variable to persist data within Transactions

This was a great idea taken from Three Really Handy Tricks With SQL Server Table Variables post.

1.Create a table variable @LOG which stores log text for each step and maybe some other key data about the step.
2.In each step, INSERT a row into @LOG.
3.When rollback occurs, INSERT another row in @LOG giving the reason for the rollback.
4.ROLLBACK
5.SELECT * FROM @LOG

All the code calling this batch has to do is interrogate the returned record set. If its last record is the one recording the ROLLBACK, then it knows something went wrong and can alert the user somehow, giving the full log of what happened. You cannot do this with a temporary table because temporary tables are transactional so the rollback will erase the log.