Save result from stored procedure in variable

[lechuck]

I have one Stored procedure that looks like this

CREATE PROCEDURE sp_test
 @c
AS
 SELECT a, b, c FROM my_table WHERE c=@c

Now I want to execute this sp whithin another sp.
To store the result in a variable when selecting from a table is easy (select @myVar=a FROM my_table)
but how does it works when the result is retrived from a stored procedure insteed of a table?

If the sp returns a parameter I know how to do it, but this sp dosn't return a parameter.
Is this possible or do I have to make a view or rewrite the sp.

 

[JohnDTampaBay]

This stored procedure produces a result set, so you will need to capture that result set into a table (temp or static) in order to do further processing on it.

Create a table that is the same structure as the result set: three columns (a, b, c) of whatever datatype. Then use an INSERT...EXEC statement to capture the results. Good luck!

CREATE TABLE ResultsTable (
   a  int
 , b  int
 , c  int
)

INSERT INTO ResultsTable (a, b, c)
EXEC sp_test @c = SomeValue

SELECT * FROM ResultsTable

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)