MS SQL:抑制存储过程中调用的存储过程的返回值 [英] MS SQL: Suppress return value of stored procedure called in stored procedure

问题描述

我想我遇到了与 kcrumley 在问题通过经典 ASP 从另一个存储过程调用存储过程的问题".然而，他的问题并没有真正包含解决方案，所以我会再试一次，加入我自己的观察:

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:

我有两个存储过程:

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

请注意，这两个过程都包含SET NOCOUNT ON".当我执行call_return_1_and_return_2"时，我仍然得到两个记录集.首先是值 1，然后是值 2.

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.

这让 ASP(经典的 VBScript ASP)偏离轨道.

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

关于如何抑制第一个结果集的任何提示?为什么即使有 NOCOUNT 也会出现?

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

跳过 ASP 中的第一个记录集不是一个选项.我需要一个仅限数据库"的解决方案.

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

推荐答案

导致这种情况的不是 NOCOUNT，您的存储过程都有一个选择，因此每个都进入自己的结果集.这可以通过更改您的第一个存储过程以使用输出参数将数字 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.

尝试这样的事情

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'

这篇关于MS SQL:抑制存储过程中调用的存储过程的返回值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！