在另一个存储过程中返回一个存储过程的输出参数

2021-09-10 00:00:00 tsql sql-server-2008 sql-server

我试图找出在另一个存储过程中执行存储过程时如何返回存储过程的输出参数:

I'm trying to find out how to return the output parameter of a stored procedure when executing the stored procedure inside another stored procedure:

 CREATE PROCEDURE Test1

 EXEC SpWithOutputID -- Outputs @ID

 SELECT @ID as ID -- Output @ID now being used in this SP

这当然不是我的代码，只是一个例子，可以这样做吗?

This is of course not my code, but just an example, is it possible to do this?

示例 2:--这里@ID返回Null

Example 2: --Here @ID returns Null

 CREATE PROCEDURE Test1
 As 
 DECLARE @ID int

 EXEC SpWithOutputID @ID = @ID OUTPUT -- Outputs @ID

 SELECT @ID as ID -- Output @ID now being used in this SP

示例 3:--这里@ID返回一个Int

Example 3: --Here @ID returns an Int

 CREATE PROCEDURE Test1
 As 

 EXEC SpWithOutputID -- Outputs @ID

推荐答案

如果这根本不是输出参数问题，而是结果集，那么猜测 SpWithOutputID 确实是像这样(返回一个带有单行单列的 SELECT):

If this isn't really an output parameter issue at all, but rather a result set, then taking a guess that SpWithOutputID does something like this (returns a SELECT with a single row and single column):

CREATE PROCEDURE dbo.SpWithOutputID
AS
BEGIN
    SET NOCOUNT ON;

    SELECT ID = 4;
END
GO

然后 Test1 可能看起来像这样:

Then Test1 could look like this:

CREATE PROCEDURE dbo.Test1
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @ID INT;

    CREATE TABLE #x(ID INT);

    INSERT #x EXEC dbo.SpWithOutputID;

    SELECT TOP (1) @ID = ID FROM #x;

    DROP TABLE #x;
END
GO

<小时><小时>

但这对你来说是不是看起来很乱?对于单个标量值，它确实应该以这种方式工作:

But doesn't that look really messy to you? It really should work this way for single, scalar values:

CREATE PROCEDURE dbo.SpWithOutputID
    @ID INT OUTPUT 
AS
BEGIN
    SET NOCOUNT ON;

    SELECT @ID = 4; 
END 
GO

现在使用真正的输出参数要简单得多:

Now it is much simpler to consume what is really an output parameter now:

CREATE PROCEDURE dbo.Test1
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @ID INT;

    EXEC dbo.SpWithOutputID @ID = @ID OUTPUT;

    SELECT @ID;
END
GO

相关文章