How to store MSSQL PRINT output to a variable

Question

On a MS SQL 2008 R2, I want to be able to catch the messages output into a variable. I need to run a script on many databases. Catch the message output (messages from print or raiserror) and log it into table. I need to do this from inside of a stored procedure.

For this script

Declare @sqlscript nvarchar(500)

Set @sqlscript = 
'select * from sys.objects
raiserror (''My raised error'', 10,1)
select * from sys.schemas
print ''my print'''

EXEC sp_executesql @sqlscript

I would like to get

My raised error    
my print

or

(60 row(s) affected)  
My raised error

(21 row(s) affected)  
my print

Update
I've decided to go with @rs suggestion. Logging into a table is the easiest way for me. Using a SP to log (to keep the code clean), plus a small regex to refactor all my scripts. The solution will be ready for tomorrow. Thanks a lot.

Answer 1

You could probably use the CLR and a technique similar to this:

http://blogs.msdn.com/b/mattm/archive/2007/09/13/capture-print-messages-from-a-stored-procedure.aspx

But the best option is to probably EXEC into a temporary table which simply has a single varchar column:

http://msdn.microsoft.com/en-us/library/aa175921%28v=sql.80%29.aspx