如何从存储过程将SELECT语句转储到SQL Server mgmt studio中的“消息”窗格？

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?

我可以在存储过程中使用PRINT语句来调试我的代码。我在SQL Server Management Studio的“消息”选项卡中看到了输出。如何将一个或多个整个SELECT语句输出转储到该消息选项卡?

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.

我的存储过程返回多个输出变量,因此返回单个数据集不是一个选项。我正在努力寻找一种调试复杂程序的好方法。

4 个解决方案

#1

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:

获取查询的全部内容以打印到消息窗口可能会比它的价值更麻烦。相反,我建议从查询窗口调试该过程。您可以做的是在过程中添加一个可选参数,默认值为NULL。您的应用程序不会通过它,因此您可以利用此优势。例如:

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

然后,当您从查询窗口调用此过程时,只需将值1传递给该@Debug参数的过程

#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.

设置“结果到文本”选项,结果和消息选项卡将合并到一个选项卡,组合您的PRINT和SELECT语句。

To set Results to Text, either:

要将结果设置为文本,可以:

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

查询菜单\结果到\结果到文本

#3

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.

您可以从有问题的存储过程中填充临时表或表变量,然后在调用存储过程后从这些表中进行选择。确保删除并重新创建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;

#4

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查询很方便,它将结果输出到Results窗格,实际输出在另一个结果集中。有时我会添加一个“标识符”列,以帮助识别结果。例如:

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

当存储的proc准备就绪时,只需删除这些脚手架语句

#1

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