如何抑制从 SQL Server 中的另一个存储过程调用的存储过程的 SELECT 输出？

How to Suppress the SELECT Output of a Stored Procedure called from another Stored Procedure in SQL Server?

提问by Dave Baghdanov

I'm not talking about doing a "SET NOCOUNT OFF". But I have a stored procedure which I use to insert some data into some tables. This procedure creates a xml response string, well let me give you an example:

我不是在谈论做一个“SET NOCOUNT OFF”。但是我有一个存储过程，用于将一些数据插入某些表中。此过程创建一个 xml 响应字符串，让我举个例子：

CREATE PROCEDURE [dbo].[insertSomeData] (@myParam int) AS
DECLARE @reply varchar(2048)

... Do a bunch of inserts/updates...

SET @reply = '<xml><big /><outputs /></xml>'
SELECT @reply
GO

So I put together a script which uses this SP a bunch of times, and the xml "output" is getting to be too much (it's crashed my box once already).

所以我整理了一个多次使用这个 SP 的脚本，而 xml“输出”变得太多了（它已经把我的盒子撞坏了一次）。

Is there a way to suppress or redirect the output generated from this stored procedure? I don't think that modifying this stored procedure is an option.

有没有办法抑制或重定向从此存储过程生成的输出？我不认为修改这个存储过程是一种选择。

thanks.

谢谢。

---

---

I guess i should clarify. This SP above is being called by a T-SQL Update script that i wrote, to be run through enterprise studio manager, etc.

我想我应该澄清一下。上面的这个 SP 由我编写的 T-SQL 更新脚本调用，通过企业工作室管理器等运行。

And it's not the most elegant SQL i've ever written either (some psuedo-sql):

它也不是我写过的最优雅的 SQL（一些伪 sql）：

WHILE unprocessedRecordsLeft
  BEGIN
    SELECT top 1 record from updateTable where Processed = 0
    EXEC insertSomeData @param = record_From_UpdateTable
  END

So lets say the UpdateTable has some 50k records in it. That SP gets called 50k times, writing 50k xml strings to the output window. It didn't bring the sql server to a stop, just my client app (sql server management studio).

假设 UpdateTable 中有大约 50k 条记录。该 SP 被调用 50k 次，将 50k xml 字符串写入输出窗口。它没有停止 sql server，只是我的客户端应用程序（sql server management studio）。

采纳答案by Dave Baghdanov

I think I found asolution.

我想我找到了解决方案。

So what i can do now in my SQL script is something like this (sql-psuedo code):

所以我现在可以在我的 SQL 脚本中做的是这样的（sql-psuedo 代码）：

create table #tmp(xmlReply varchar(2048))
while not_done
  begin
    select top 1 record from updateTable where processed = 0
    insert into #tmp exec insertSomeData @param=record
  end
drop table #tmp

Now if there was a even more efficient way to do this. Does SQL Server have something similar to /dev/null? A null table or something?

现在，如果有更有效的方法来做到这一点。SQL Server 是否有类似于 /dev/null 的东西？空表什么的？

回答by Serj Sagan

The answer you're looking for is found in a similar SO questionby Josh Burke:

您正在寻找的答案在Josh Burke的类似 SO 问题中找到：

-- Assume this table matches the output of your procedure
DECLARE @tmpNewValue TABLE ([Id] int, [Name] varchar(50))

INSERT INTO @tmpNewValue 
  EXEC ProcedureB

SELECT * FROM @tmpNewValue

回答by dyslexicanaboko

Answering the question, "How do I suppress stored procedure output?" really depends on what you are trying to accomplish. So I want to contribute what I encountered:

回答“如何抑制存储过程输出？”的问题。真的取决于你想要完成什么。所以我想贡献我遇到的东西：

I needed to supress the stored procedure (USP) output because I just wanted the row count (@@ROWCOUNT) from the output. What I did, and this may not work for everyone, is since my query was already going to be dynamic sql I added a parameter called @silentExecution to the USP in question. This is a bit parameter which I defaulted to zero (0).

我需要抑制存储过程 (USP) 输出，因为我只想要输出中的行数 (@@ROWCOUNT)。我所做的，这可能不适用于所有人，因为我的查询已经是动态 sql，我向相关 USP 添加了一个名为 @silentExecution 的参数。这是一个位参数，我默认为零 (0)。

Next if @silentExecution was set to one (1) I would insert the table contents into a temporary table, which is what would supress the output and then execute @@ROWCOUNT with no problem.

接下来，如果@silentExecution 设置为一 (1)，我会将表内容插入到临时表中，这将抑制输出，然后毫无问题地执行 @@ROWCOUNT。

USP Example:

USP 示例：

CREATE PROCEDURE usp_SilentExecutionProc
    @silentExecution bit = 0
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @strSQL VARCHAR(MAX);

    SET @strSQL = '';

    SET @strSQL = 'SELECT TOP 10 * ';

    IF @silentExecution = 1
         SET @strSQL = @strSQL + 'INTO #tmpDevNull ';

    SET @strSQL = @strSQL +     
    'FROM dbo.SomeTable ';

    EXEC(@strSQL);
END
GO

Then you can execute the whole thing like so:

然后你可以像这样执行整个事情：

EXEC dbo.usp_SilentExecutionProc @silentExecution = 1;
SELECT @@ROWCOUNT;

The purpose behind doing it like this is if you need the USP to be able to return a result set in other uses or cases, but still utilize it for just the rows.

这样做的目的是，如果您需要 USP 能够在其他用途​​或情况下返回结果集，但仍仅将其用于行。

Just wanted to share my solution.

只是想分享我的解决方案。

回答by John Saunders

Man, this is seriously a case of a computer doing what you told it to do instead of what you wantedit to do.

伙计，这真的是一个计算机按照你告诉它做的事情而不是你想让它做的事情。

If you don't want it to return results, then don't askit to return results. Refactor that stored procedure into two:

如果您不希望它返回结果，则不要要求它返回结果。将该存储过程重构为两个：

CREATE PROCEDURE [dbo].[insertSomeData] (@myParam int) AS
BEGIN
DECLARE @reply varchar(2048)

--... Do a bunch of inserts/updates...

EXEC SelectOutput
END
GO

CREATE PROCEDURE SelectOutput AS
BEGIN
SET @reply = '<xml><big /><outputs /></xml>'
SELECT @reply
END

回答by Andomar

From which client are you calling the stored procedure? Say it was from C#, and you're calling it like:

您从哪个客户端调用存储过程？假设它来自 C#，并且您这样称呼它：

var com = myConnection.CreateCommand();
com.CommandText = "exec insertSomeData 1";
var read = com.ExecuteReader();

This will not yet retrieve the result from the server; you have to call Read() for that:

这还不会从服务器检索结果；你必须为此调用 Read() ：

read.Read();
var myBigString = read[0].ToString();

So if you don't call Read, the XML won't leave the Sql Server. You can even call the procedure with ExecuteNonQuery:

因此，如果您不调用 Read，则 XML 不会离开 Sql Server。您甚至可以使用 ExecuteNonQuery 调用该过程：

var com = myConnection.CreateCommand();
com.CommandText = "exec insertSomeData 1";
com.ExecuteNonQuery();

Here the client won't even ask for the result of the select.

在这里，客户端甚至不会询问选择的结果。

回答by Lin

I have recently come across with a similar issue while writing a migration script and since the issue was resolved in a different way, I want to record it. I have nearly killed my SSMS Client by running a simple while loop for 3000 times and calling a procedure.

我最近在编写迁移脚本时遇到了类似的问题，由于问题以不同的方式解决，我想记录下来。通过运行一个简单的 while 循环 3000 次并调用一个过程，我几乎杀死了我的 SSMS 客户端。

DECLARE @counter INT
SET @counter = 10
WHILE @counter > 0 
BEGIN 
    -- call a procedure which returns some resultset
    SELECT  @counter-- (simulating the effect of stored proc returning some resultset)
    SET @counter = @counter - 1
END

The script result was executed using SSMS and default option on query window is set to show “Results to Grid”[Ctrl+d shortcut].

脚本结果是使用 SSMS 执行的，查询窗口的默认选项设置为显示“结果到网格”[Ctrl+d 快捷键]。

Easy Solution:Try setting the results to file to avoid the grid to be built and painted on the SSMS client. [CTRL+SHIFT+F keyboard shortcut to set the query results to file].

简单的解决方案：尝试将结果设置为文件，以避免在 SSMS 客户端上构建和绘制网格。[CTRL+SHIFT+F 键盘快捷键设置查询结果到文件]。

This issue is related to : stackoverflow query

此问题与：stackoverflow 查询有关

回答by MikeW

I don't know if SQL Server has an option to suppress output (I don't think it does), but the SQL Query Analyzer has an option (under results tab) to "Discard Results".

我不知道 SQL Server 是否有抑制输出的选项（我认为没有），但 SQL 查询分析器有一个选项（在结果选项卡下）“丢弃结果”。

Are you running this through isql?

你是通过 isql 运行的吗？

回答by Hafthor

You could create a SQL CLR stored procedure that execs this. Should be pretty easy.

您可以创建一个执行此操作的 SQL CLR 存储过程。应该很容易。

回答by Jeff

You said your server is crashing. What is crashing the application that consumes the output of this SQL or SQL Server itself (assuming SQL Server).

你说你的服务器崩溃了。是什么使消耗此 SQL 或 SQL Server 本身（假设为 SQL Server）输出的应用程序崩溃。

If you are using .Net Framework application to call the stored procedure then take a look at SQLCommand.ExecuteNonQuery. This just executes stored procedure with no results returned. If problem is at SQL Server level then you are going to have to do something different (i.e. change the stored procedure).

如果您使用 .Net Framework 应用程序来调用存储过程，请查看 SQLCommand.ExecuteNonQuery。这只是执行存储过程，没有返回结果。如果问题出在 SQL Server 级别，那么您将不得不做一些不同的事情（即更改存储过程）。

回答by plozi

ever tried SET NOCOUNT ON;as an option?

曾经尝试过SET NOCOUNT ON;作为一种选择吗？