当从存储过程执行存储过程时，如何禁用查询结果?

Within a stored procedure, another stored procedure is being called within a cursor. For every call, the SQL Management Studio results window is showing a result. The cursor loops over 100 times and at that point the results window gives up with an error. Is there a way I can stop the stored procedure within the cursor from outputting any results?

在存储过程中，在游标中调用另一个存储过程。对于每个调用，SQL Management Studio results窗口都显示一个结果。游标循环超过100次，此时结果窗口将放弃一个错误。是否有一种方法可以阻止光标内的存储过程输出任何结果?

  WHILE @@FETCH_STATUS = 0
  BEGIN
    EXEC @RC = dbo.NoisyProc
    SELECT @RValue2 = 1 WHERE @@ROWCOUNT = 0
    FETCH NEXT FROM RCursor INTO @RValue1, @RValue2
  END

Thanks!

谢谢!

6 个解决方案

#1

you could insert the results into a temp table, then drop the temp table

您可以将结果插入到临时表中，然后删除临时表

create table #tmp (columns)

while
    ...
    insert into #tmp exec @RC=dbo.NoisyProc
    ...
end
drop table #tmp

otherwise, can you modify the proc being called to accept a flag telling it not to output a result-set?

否则，是否可以修改被调用的proc以接受一个标志，告诉它不要输出结果集?

#2

You can discard the resultsets in SQL Server Mgmt Studio 2005 by following the steps below:

您可以通过以下步骤丢弃SQL Server Mgmt Studio 2005中的resultset:

• Right-click in the query window
• Choose "Query Options"
• Click on the "Results" "node" in the left panel tree view
• Check "Discard results after execution" in the center/right of the form

•右键单击查询窗口•选择“查询选项”•单击左侧面板树视图中的“结果”“节点”•检查表单中心/右侧的“执行后丢弃结果”

You can try it on

你可以试穿一下

DECLARE @i int
SET @i = 1
WHILE (@i <= 100)
  BEGIN
    SELECT @i as Iteration
    SET @i = @i + 1
  END

#3

I know this question is old, but you could set the SET NOCOUNT ON to prevent the SP to output a message for every row.

我知道这个问题很老，但是您可以设置NOCOUNT，以防止SP向每一行输出一个消息。

#4

Cursors bad. Don't reuse stored proc code if it means you have to do a set-based function with a cursor. Better for performance to write the code in a set-nbased fashion.

游标坏。如果您必须使用游标进行基于集的函数，则不要重用存储的proc代码。更好的性能是以基于集合的方式编写代码。

I think I'm concerned that you are more concerned with supressing the messages than you are that you have an error in the cursor.

我认为我担心的是，您更关心的是压缩消息，而不是您在游标中有错误。

#5

Probably the error comes from too much recordsets being returned, rather than a logic flaw on your SP or the cursor itself. Look at this example:

可能错误是由于返回了太多的记录集，而不是SP或游标本身的逻辑缺陷。看看这个例子:

DECLARE @I INT
SET @I=0
WHILE @I<200 BEGIN
    SELECT * FROM INFORMATION_SCHEMA.TABLES
    SET @I = @I + 1
END

Will run a number of times (slightly more than 100) then fail with:

将运行多次(略超过100次)，然后失败:

The query has exceeded the maximum number of result sets that can be displayed in the results grid. Only the first 100 result sets are displayed in the grid.

查询已超过可以在结果网格中显示的结果集的最大数量。网格中只显示前100个结果集。

The SSMS has a limit on the number of record-sets it can show you. One quick way to by-pass that limitation is to press Ctrl+T (or menu Query->Results to->Results to Text) to force the output to be in plain text, rather than table-like recordsets. You'll reach another limit eventually (the results window can't handle an infinite amount of text output) yet it will be far greater.

SSMS对记录集的数量有限制。绕过这个限制的一种快速方法是按Ctrl+T(或菜单查询->结果到->结果到文本)来强制输出为纯文本，而不是像表那样的记录集。最终您将达到另一个极限(结果窗口不能处理无限的文本输出)，但是它将会更大。

In the sample above you don't get the error after changing the results to be in text form!

在上面的示例中，您将结果更改为文本形式后不会得到错误!

#6

Place:

的地方:

SET ROWCOUNT OFF
/* the internal SP */
SET ROWCOUNT ON

wrap that around the internal SP, or you could even do it around the SELECT statement from the originating query, that will prevent results from appearing.

将其包装在内部SP上，或者您甚至可以围绕源自原始查询的SELECT语句进行，这样将防止结果出现。

#1