MS SQL：抑制存储过程中调用的存储过程的返回值

I think I have the same problem as kcrumley describes in the question "Problem calling stored procedure from another stored procedure via classic ASP". However his question does not really include an solution, so I'll give it another shot, adding my own observations:

我认为我遇到的问题与kcrumley在“通过经典ASP从另一个存储过程调用存储过程的问题”中所描述的问题相同。然而，他的问题并没有真正包含一个解决方案，所以我会再给它一个镜头，添加我自己的观察结果：

I have two stored procedures:

我有两个存储过程：

CREATE PROCEDURE return_1 AS BEGIN
    SET NOCOUNT ON;
    SELECT 1
END

CREATE PROCEDURE call_return_1_and_return_2 AS BEGIN
    SET NOCOUNT ON;
    EXEC return_1
    SELECT 2
END

Note that both procedures contain "SET NOCOUNT ON". When I execute "call_return_1_and_return_2" I still get two record sets. First the value 1, then the value 2.

请注意，这两个过程都包含“SET NOCOUNT ON”。当我执行“call_return_1_and_return_2”时，我仍然可以获得两个记录集。首先是值1，然后是值2。

That throws ASP (classic VBScript ASP) off the tracks.

这会抛出ASP（经典的VBScript ASP）。

Any hints on how I can suppress the first result set? Why is it there even with NOCOUNT?

关于如何抑制第一个结果集的任何提示？为什么即使有NOCOUNT呢？

Skipping the first record set in ASP is not an option. I need a "database only" solution.

跳过ASP中的第一个记录集不是一种选择。我需要一个“仅限数据库”的解决方案。

3 个解决方案

#1

Its not the NOCOUNT thats causing this, your stored procedures have a select each so each one is coming in its own result set. This could be avoided by changing your first stored procedure to use output parameters to pass the number 1 back rather than doing a select. The second stored procedure could then examine the output parameter to get the data it needs to run.

它不是引起这种情况的NOCOUNT，你的存储过程每个都有一个select，所以每个都有自己的结果集。通过更改第一个存储过程以使用输出参数将数字1传回而不是执行选择，可以避免这种情况。然后，第二个存储过程可以检查输出参数以获取它需要运行的数据。

Try something like this

尝试这样的事情

CREATE PROCEDURE Proc1
(
    @RetVal INT OUTPUT
)
AS
SET NOCOUNT ON
SET @RetVal = 1

CREATE PROCEDURE Proc2
AS
SET NOCOUNT ON
DECLARE @RetVal int
EXEC    [dbo].[Proc1]
        @RetVal = @RetVal OUTPUT
SELECT  @RetVal as N'@RetVal'

#2

As Matt points out in his comment, neither solution really 'swallow' the first resultset. I don't know why you'd want this but you can 'swallow' the result of the first exec by using a table variable. It must match the exact amount and type of the result set's columns. Like so:

正如Matt在他的评论中指出的那样，两种解决方案都没有真正“吞下”第一个结果集。我不知道你为什么要这样，但你可以通过使用表变量'吞下'第一个exec的结果。它必须与结果集列的确切数量和类型相匹配。像这样：

CREATE PROCEDURE return_1 AS 
    SET NOCOUNT ON;
    SELECT 1
GO
CREATE PROCEDURE call_return_1_and_return_2 AS 
    SET NOCOUNT ON;
    DECLARE @Result TABLE (res int)
    insert into @Result EXEC return_1
    SELECT 2
GO

#3

Those are not return variables, but output record sets. I guess that as soon as SQL server flushes output to client, you're screwed and can't take it back.

这些不是返回变量，而是输出记录集。我想只要SQL服务器将输出刷新到客户端，你就会被搞砸，无法收回。

I would solve this by adding a parameter to SP return_1, that would control if return_1 would select records or just do stuff and silently exit.

我会通过向SP return_1添加一个参数来解决这个问题，这将控制return_1是选择记录还是只是做东西并静默退出。

#1