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.

Accepted Answer

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.

How to SELECT FROM stored procedure

Solution

Problem