2

How can I do something along the following lines?

DECLARE @Var1 VARCHAR(200)
DECLARE @Var2 VARCHAR(200)

SET @Var1 = value1 from sp_someotherprocedure(@id)
SET @Var2 = value1 from sp_someotherprocedure(@id)
Taryn
  • 242,637
  • 56
  • 362
  • 405
Mark Highfield
  • 443
  • 3
  • 9
  • 24
  • What "results" of a stored procedure do you mean? A stored procedure can have at least three different kinds of "results". – RBarryYoung May 03 '13 at 20:54

4 Answers4

2

I've created a simple example how to from results of stored procedure. In order to set a variable to the return value from stored procedure you need to use OUTPUT parameter with stored procedure, which will return the value.

CREATE PROCEDURE YourStoredProcedure 
(

    @i    int,
    @result int OUTPUT
)
AS
BEGIN
SET @i = 1
Set @result = @i + 10
return @result
END

-- Code to set the output paramter

DECLARE @outputparameter AS int
exec @outputparameter =  YourStoredProcedure 10, @outputparameter output
print @outputparameter
Vishwanath Dalvi
  • 35,388
  • 41
  • 123
  • 155
0

Like this:

EXEC @Var1 = sp_someotherprocedure(@id)
Melanie
  • 3,021
  • 6
  • 38
  • 56
0

Stored procedures could return many result sets. Therefore, it is not meant to be used with a SELECT.
They Could be used in a case where you have OUTPUT parameters You should look into Functions and then SELECT from it. 

CREATE FUNCTION SalesByStore (@storeid varchar(30))
RETURNS TABLE AS
RETURN (SELECT t.title, s.qty
    FROM   sales s
    JOIN   titles t ON t.title_id = s.title_id
    WHERE  s.stor_id = @storeid)

Then you call it like : 

SELECT * FROM SalesByStore('6380')
phadaphunk
  • 12,785
  • 15
  • 73
  • 107
0

I'm pretty late to the party on this one, but just ran into this issue myself. The solution my team and I came up with is to execute the result of the sproc into a temp table, then select top 1 (or whatever meets your criteria) into local variable(s). So using the OP's example might look something like this:

DECLARE @Var1 VARCHAR(200)
DECLARE @Var2 VARCHAR(200)
DECLARE @sprocResult TABLE(val1 INT, val2 INT)

INSERT INTO @sprocResult (val1, val2)
EXEC sp_someotherprocedure @id

SELECT TOP 1 
    @Var1 = val1,
    @Var2 = val2
FROM @sprocResult
John Pearson
  • 1