如何在另一个srored过程中的一个存储过程中使用SELECT的结果？

I have a stored procedure that computes several values and SELECTs them:

我有一个存储过程来计算几个值并选择它们：

CREATE PROCEDURE [dbo].[MyProc]
AS
   DECLARE @value1 int;
   DECLARE @value2 int;
   SET @value1 =...
   IF( @value1 IS NULL ) 
       RETURN 0;
   SET @value2 =...
   SELECT @value1 AS Value1, @value2 AS Value2;
RETURN 0;

I know I can turn that into a table function but I'd rather not do that because of RETURN in the middle - sometimes there's just nothing to return.

我知道我可以把它变成一个表函数，但我宁愿不这样做，因为中间有RETURN - 有时候没有什么可以返回的。

I want to call that stored procedure from another stored procedure and use the values retrieved by SELECT in the other procedure. How do I do that?

我想从另一个存储过程调用该存储过程，并在另一个过程中使用SELECT检索的值。我怎么做？

2 个解决方案

#1

You can create a temptable and insert both values in there.

您可以创建一个temptable并在其中插入两个值。

CREATE TABLE #Temp (value1 int, value2 int)

INSERT INTO #Temp (value1, value2)
EXEC [dbo].[MyProc]

If Value1 is NULL there will be no record in #Temp, and in this case you don't need the return 0.

如果Value1为NULL，则#Temp中没有记录，在这种情况下，您不需要返回0。

But if it is not your goal, and you need return 0, then you should use the @value1 and @value2 as output parameters.

但如果它不是你的目标，并且你需要返回0，那么你应该使用@ value1和@ value2作为输出参数。

#2

You can add output parameters:

您可以添加输出参数：

CREATE PROCEDURE [dbo].[MyProc]
(
  @value1 int = null output,
  @value2 int = null output
)
AS

   SET @value1 =...
   IF( @value1 IS NULL ) 
       RETURN 0;
   SET @value2 =...
   SELECT @value1 = Value1, 
          @value2 = Value2;
RETURN 0;

and use it:

并使用它：

declare @v1 int, 
        @v2 int

exec MyProc @v1 out, @v2 out

select @v1, @v2

or if you need more values you can use temporary table

或者如果您需要更多值，可以使用临时表

create table #tmp
(
  val1 int null,
  val2 int null 
)

CREATE PROCEDURE [dbo].[MyProc]   
AS

   SET @value1 =...
   IF( @value1 IS NULL ) 
       RETURN 0;
   SET @value2 =...

   insert into #tmp
   SELECT Value1, Value2
   from tab

RETURN 0;

and use it:

并使用它：

create table #tmp
(
  val1 int null,
  val2 int null 
)

exec MyProc 

select *
from #tmp

drop table #tmp