如何将exec存储过程的结果设置为变量?

问题描述：

我需要执行一个存储过程并将其标量结果存储到存储过程中的局部变量中.

I need to exec a stored procedure and store its scalar result to a local variable inside a stored procedure.

如何实施?

例如

CREATE PROCEDURE [dbo].GetNthNo AS 
 DECLARE @a INT
 DECLARE @d INT
 DECLARE @n INT
DECLARE @S INT
SET @S=EXEC spGetNthNo @a,@d,@n

SELECT @S

请帮忙.

答

代替:

SET @S=EXEC spGetNthNo @a,@d,@n

您需要:

EXEC @S = spGetNthNo @a,@d,@n

然后在程序中，您需要类似的东西:

And then within the procedure, you need something like:

RETURN 100

或:

RETURN @x

用于在过程执行后为@S 设置的值.

for the value you want for @S after the procedure executes.

您也可以使用输出参数.组合示例:

You can also use output parameters. Combined example:

IF OBJECT_ID('tempdb..#example') IS NOT NULL DROP PROCEDURE #example
GO

CREATE PROCEDURE #example
  @output_param INT OUTPUT
AS BEGIN
   SET @output_param = 100
   RETURN 200
END
GO

DECLARE @return INT, @param INT
EXEC @return = #example @output_param = @param OUTPUT
SELECT @return as [return value], @param as [output parameter]

如何将exec存储过程的结果设置为变量?

相关推荐