How to SELECT FROM stored procedure

Question

I have a stored procedure that returns rows:

CREATE PROCEDURE MyProc
AS
BEGIN
    SELECT * FROM MyTable
END

My actual procedure is a little more complicated, which is why a stored procedure is necessary.

Is it possible to select the output by calling this procedure?

Something like:

SELECT * FROM (EXEC MyProc) AS TEMP

I need to use SELECT TOP X, ROW_NUMBER, and an additional WHERE clause to page my data, and I don't really want to pass these values as parameters.

Answer 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 ... 

Answer 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.

Answer 3

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

INSERT INTO #tab EXEC MyProc

Answer 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

Answer 5

You must read about OPENROWSET and OPENQUERY

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

Answer 6

It is not necessary use a temporary table.

This is my solution

SELECT  *  FROM    
OPENQUERY(YOURSERVERNAME, 'EXEC MyProc @parameters')
WHERE somefield = anyvalue