Read/Use Stored Procedure result data in SQL query

It is better to use a 'User defined function' or a 'View' instead of the Stored procedure if

you want to access the result records in your query. 

Anyway, if you want to access the data from a stored procedure, you need to store the

result first in some temporary table and use it in your query. 

You can use a table variable as below.

DECLARE @tbl TABLE(field1 int ,field2 varchar(500) ,...)      

    INSERT INTO @tbl        

    EXEC myprocedure @param ..

SELECT * FROM @tbl