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

Does it have to be in the messages pane ? I always find it convenient to just run the select query, which outputs the results into the Results pane, with the actual output in another result set. Sometimes I include an 'identifier' column that helps identify the results. For instance:

select 'checkpoint1',a,b,c from table1
....
select 'table @a', *  from @a
....
<actual query here>

When the stored proc is ready to go just remove these scaffolding statements

Answer 2

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 3

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

Answer 4

You might populate a temporary table, or table variable, from within your problematic stored procedure, then select from those tables after calling the stored procedure. Make sure to drop and recreate any temporary tables in the sp.

CREATE TABLE ##t1 (x int)
GO

CREATE PROCEDURE Foo AS
BEGIN
  TRUNCATE TABLE ##t1
  INSERT INTO ##t1 SELECT column FROM table;
END
GO

exec Foo;
select x from ##t1;