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

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.

Answer 1

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.