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

发布于 2024-07-06 16:40:58 字数 337 浏览 12 评论 0原文

在一个存储过程中，另一个存储过程在游标内被调用。对于每次调用，SQL Management Studio 结果窗口都会显示结果。光标循环超过 100 次，此时结果窗口会出现错误并放弃。有没有办法阻止游标内的存储过程输出任何结果？

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

谢谢！

原文

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?

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

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

旧人九事 2024-07-13 16:40:58

您可以放弃 SQL Server Mgmt Studio 2005 中的结果集
请按照以下步骤操作：

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

你可以试穿一下

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

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

• 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

回复收藏 0 原文

归途 2024-07-13 16:40:58

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

create table #tmp (columns)

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

否则，您可以修改被调用的过程以接受告诉它不要输出结果集的标志吗？

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?

回复收藏 0 原文

墨小沫ゞ 2024-07-13 16:40:58

我知道这个问题已经很老了，但是您可以设置 SET NOCOUNT ON 来防止 SP 为每一行输出消息。

回复收藏 0 原文

無心 2024-07-13 16:40:58

游标坏了。如果这意味着您必须使用游标执行基于集合的函数，请不要重用存储过程代码。以基于 set-n 的方式编写代码可以获得更好的性能。

我想我担心您更关心抑制消息而不是光标中出现错误。

回复收藏 0 原文

旧情勿念 2024-07-13 16:40:58

该错误可能来自于返回的记录集过多，而不是 SP 或游标本身的逻辑缺陷。看这个例子：

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

将运行多次（略多于 100 次）然后失败，并显示：

查询超出了结果网格中可显示的最大结果集数。网格中仅显示前 100 个结果集。

SSMS 对它可以向您显示的记录集数量有限制。
绕过该限制的一种快速方法是按 Ctrl+T（或菜单“查询”->“结果到文本”）强制输出为纯文本格式，而不是类似表格的记录集。最终您将达到另一个限制（结果窗口无法处理无限量的文本输出），但它会大得多。

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

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:

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:

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.

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.

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

回复收藏 0 原文

方圜几里 2024-07-13 16:40:58

此页面已过时，回复也已过时。但是，最佳答案尚未被投票到顶部。我想这是因为没有提供足够的解释。

使用 NOCOUNT 设置。每个人都应该看看 NOCOUNT 设置。默认设置为关闭。

即使在新数据库上普遍更改 this 的默认设置也可能会导致某些编码员\用户感到困惑。我建议使用在更改设置之前捕获设置，然后将其设置回来的方法。下面的示例脚本显示了这一点，该脚本演示了 NOCOUNT 设置的使用。

这是一篇好文章。
https://www .sqlshack.com/set-nocount-on-statement-usage-and-performance-benefits-in-sql-server/

DROP TABLE IF EXISTS TestTable
GO
CREATE TABLE TestTable (ID INT, TestText VARCHAR (40))
GO

-- Get the Original NOCOUNT setting and save it to @OriginalNoCountSettingIsOn
DECLARE @options INT
SET @options = @@OPTIONS
DECLARE @OriginalNoCountSettingIsOn AS bit
SET @OriginalNoCountSettingIsOn = IIF(( (512 & @@OPTIONS) = 512 ),1,0)

-- Now set NOCOUNT ON to suppress result output returned from INSERTS 
-- Note - this does not affect @@ROWCOUNT values from being set 
SET NOCOUNT ON --  <---- Try running script with SET NOCOUNT ON and SET NOCOUNT OFF to See difference

INSERT INTO TestTable (ID, TestText)
VALUES  (0, 'Test Row 1')

INSERT INTO TestTable (ID, TestText)
VALUES  (0, 'Test Row 2'),
        (0, 'Test Row 3'),
        (0, 'Test Row 4')

INSERT INTO TestTable (ID, TestText)
VALUES  (0, 'Test Row 5')

/*Now set NOCOUNT back to original setting*/
IF @OriginalNoCountSettingIsOn = 1 
BEGIN
    SET NOCOUNT ON
END
ELSE
BEGIN
    SET NOCOUNT OFF
END 

DROP TABLE IF EXISTS TestTable
GO

This page is old and the replies are old. But, the best answer has not been upvoted to the top. I suppose it is because not enough explanation was provided.

Use the NOCOUNT setting. Everyone should look at the NOCOUNT setting. The default setting is OFF.

Changing the default setting of a this universally even on a new database may cause confusion for some coders\users. I recommend using the approach of capturing the setting before changing it, then setting it back. This is shown in the example script below which demonstrates use of the NOCOUNT setting.

Here is a good article.
https://www.sqlshack.com/set-nocount-on-statement-usage-and-performance-benefits-in-sql-server/

DROP TABLE IF EXISTS TestTable
GO
CREATE TABLE TestTable (ID INT, TestText VARCHAR (40))
GO

-- Get the Original NOCOUNT setting and save it to @OriginalNoCountSettingIsOn
DECLARE @options INT
SET @options = @@OPTIONS
DECLARE @OriginalNoCountSettingIsOn AS bit
SET @OriginalNoCountSettingIsOn = IIF(( (512 & @@OPTIONS) = 512 ),1,0)

-- Now set NOCOUNT ON to suppress result output returned from INSERTS 
-- Note - this does not affect @@ROWCOUNT values from being set 
SET NOCOUNT ON --  <---- Try running script with SET NOCOUNT ON and SET NOCOUNT OFF to See difference

INSERT INTO TestTable (ID, TestText)
VALUES  (0, 'Test Row 1')

INSERT INTO TestTable (ID, TestText)
VALUES  (0, 'Test Row 2'),
        (0, 'Test Row 3'),
        (0, 'Test Row 4')

INSERT INTO TestTable (ID, TestText)
VALUES  (0, 'Test Row 5')

/*Now set NOCOUNT back to original setting*/
IF @OriginalNoCountSettingIsOn = 1 
BEGIN
    SET NOCOUNT ON
END
ELSE
BEGIN
    SET NOCOUNT OFF
END 

DROP TABLE IF EXISTS TestTable
GO

回复收藏 0 原文

古镇旧梦 2024-07-13 16:40:58

Place：

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

将其包装在内部 SP 周围，或者您甚至可以将其包装在原始查询的 SELECT 语句周围，这将阻止结果出现。

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.

回复收藏 0 原文

~没有更多了~