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

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:

我并不是在说要做一个“固定的数目”。但是我有一个存储过程，用来将一些数据插入到一些表中。这个过程创建一个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更新脚本调用，通过enterprise studio manager等来运行。

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

它也不是我写过的最优雅的SQL(一些psuedo-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服务器停止，只是我的客户机应用程序(sql server management studio)。

10 个解决方案

#1

I think I found a solution.

我想我找到解决办法了。

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的东西?空表还是什么?

#2

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

你正在寻找的答案与Josh Burke的一个相似的问题相似:

/* Assume this table matches the output of your procedure */
DECLARE @tmpNewValue TABLE (newvalue int)

INSERT INTO @tmpNewValue 
  EXEC ProcedureB

#3

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

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

#4

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

天哪，这确实是一个电脑按你说的去做而不是按你想要的去做的情况。

If you don't want it to return results, then don't ask it 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

#5

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服务器。你甚至可以用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.

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

#6

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

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

#7

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快捷键将查询结果设置为file]。

This issue is related to : * query

这个问题与*查询有关。

#8

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查询分析器有一个选项(在results选项卡下)来“丢弃结果”。

Are you running this through isql?

你是通过isql运行这个吗?

#9

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服务器本身的输出的应用程序崩溃(假设SQL服务器)。

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框架应用程序调用存储过程，那么请查看SQLCommand.ExecuteNonQuery。它只执行存储过程，不返回结果。如果问题出现在SQL Server级别，那么您将不得不做一些不同的事情(例如更改存储过程)。

#10

-2

ever tried SET NOCOUNT ON; as an option?

曾经尝试过设置不依赖;作为一个选项?

#1