如何抑制从 SQL Server 中的另一个存储过程调用的存储过程的 SELECT 输出? [英] How to Suppress the SELECT Output of a Stored Procedure called from another Stored Procedure in SQL Server?

问题描述

我说的不是SET NOCOUNT OFF".但是我有一个存储过程，用于将一些数据插入到某些表中.这个过程创建了一个 xml 响应字符串，让我举个例子:

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:

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

所以我整理了一个多次使用这个 SP 的脚本，而 xml输出"变得太多了(它已经把我的盒子撞坏了一次).

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

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

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.

谢谢.

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

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.

而且它也不是我写过的最优雅的 SQL(一些伪 sql):

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

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

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

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

推荐答案

我想我找到了一个解决方案.

I think I found a solution.

所以我现在可以在我的 SQL 脚本中做的事情是这样的(sql-psuedo 代码):

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

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

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

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 中的另一个存储过程调用的存储过程的 SELECT 输出?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！