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

Question

Let 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 the print messages in the stored procedure test_print and save it into a variable?

Thanks.

Solution

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

Comments

[AdaTheDev]

+1, just to add from the .NET perspective, here's a prev question on capturing the output: stackoverflow.com/questions/1880471/…