问题描述
我试图找出在另一个存储过程中执行存储过程时如何返回存储过程的输出参数:
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