How do I use result of SELECT in one stored procedure in another srored procedure?

Question

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.

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?

Answer 1

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

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.

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