CODEWITHSUNDEEP
sql
Mark Highfield
Mark Highfield

Reputation: 453

Set variables from stored procedure from results of another stored procedure

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)

Upvotes: 2

Views: 8347

Answers (4)

John Pearson
John Pearson

Reputation: 1

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

Upvotes: 0

Vishwanath Dalvi
Vishwanath Dalvi

Reputation: 36671

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

Upvotes: 2

phadaphunk
phadaphunk

Reputation: 13313

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')

Upvotes: 0

Melanie
Melanie

Reputation: 3111

Like this:

EXEC @Var1 = sp_someotherprocedure(@id)

Upvotes: 0

Related Questions