Sql – How to SELECT FROM stored procedure

sqlsql serversql-server-2005stored-procedures

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.

Best Answer

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 ...
Related Topic