How can I dump a SELECT statement to the "Messages" pane in SQL Server mgmt studio from a stored procedure?

Question

I can use the PRINT statement in a stored procedure to debug my code. I see the output in the Messages tab of SQL Server Management Studio. How can I dump one or more entire SELECT statement outputs to that Messages tab?

My stored procedure returns several output variables so returning a single dataset isn't an option here. I am struggling with finding a good way to debug my complex procedures.

Answer 1

Set the "Results to Text" option and your Results and Messages tabs will be consolidated to a single tab, combining your PRINT and SELECT statements.

To set Results to Text, either:

• Control-T
• Query menu \ Results To \ Results to Text

Answer 2

Getting the entire contents of a query to print to the messages window would probably be more trouble than it's worth. Instead, I would recommend debugging the procedure from a query window. What you can do is add an optional parameter to your procedure, with a default of NULL. Your app won't be passing it, so you can use this to your advantage. Ex:

Alter Procedure Foo(@Col1 int, @col2 varchar(20), @Debug Bit = NULL)
As
SET NOCOUNT ON

If @Debug = 1
  Begin
    Select * From SomeTable Where Col1 = @Col1
  End

-- The rest of your code here

Then, when you call this procedure from a query window, simply pass in a value of 1 to the procedure for that @Debug parameter