How to SELECT FROM stored procedure

Solution 1:

You can

1. create a table variable to hold the result set from the stored proc and then
2. insert the output of the stored proc into the table variable, and then
3. use the table variable exactly as you would any other table...

... sql ....

Declare @T Table ([column definitions here])
Insert @T Exec storedProcname params 
Select * from @T Where ...

Solution 2:

You can use a User-defined function or a view instead of a procedure.

A procedure can return multiple result sets, each with its own schema. It's not suitable for using in a SELECT statement.

Solution 3:

You either want a Table-Valued function or insert your EXEC into a temporary table:

INSERT INTO #tab EXEC MyProc

Solution 4:

You need to declare a table type which contains the same number of columns your store procedure is returning. Data types of the columns in the table type and the columns returned by the procedures should be same

declare @MyTableType as table
(
FIRSTCOLUMN int
,.....
)  

Then you need to insert the result of your stored procedure in your table type you just defined

Insert into @MyTableType 
EXEC [dbo].[MyStoredProcedure]

In the end just select from your table type

Select * from @MyTableType

Solution 5:

You must read about OPENROWSET and OPENQUERY

SELECT  * 
INTO    #tmp FROM    
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')