5

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.

Just a learner
  • 26,690
  • 50
  • 155
  • 234
  • Have a look at this previous answer http://stackoverflow.com/questions/1589466/sql-server-execute-stored-procedure-with-output-parameter - Basically you need to use the OUTPUT clasue. – twoleggedhorse Mar 08 '13 at 10:57
  • I know the OUTPUT clause. My situation is that I can't modify the stored procedure and I can just invoke it. The stored procedure use a lot of PRINT statements in it. How can I get those strings from the PRINT statements? – Just a learner Mar 08 '13 at 11:00
  • 6
    I strongly disagree with marking this question as duplicate, on the basis that the duplicated question referred to does not answer it. The question referred to gives a solution in .NET, not sql. The question may not have an answer, but it's a valid question and should stand on it's own. – hktegner Jul 17 '17 at 10:47

1 Answers1

6

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.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • +1, just to add from the .NET perspective, here's a prev question on capturing the output: http://stackoverflow.com/questions/1880471/capture-stored-procedure-print-output-in-net – AdaTheDev Mar 08 '13 at 11:23