How can I capture a print message from a SQL Server stored procedure?

Question

Say I have a stored procedure like this:

begin try drop procedure test_print end try begin catch end catch; go create procedure test_print as begin print 'Hello' print 'World'; end go exec test_print 

How can I capture print messages in the test_print stored procedure and save it in a variable?

Thanks.

Answer 1

You cannot in T-SQL. The information output is always sent to the client. Therefore, you must be a customer to capture it. A simple workaround is to call a procedure from SQLCLR. Then you can simply hook up the InfoMessage event and get the calee output.