SQL Server - SELECT FROM stored procedure

sql sql-server sql-server-2005 stored-procedures
jonathanpeppers picture jonathanpeppers · Sep 29, 2009 · Viewed 948.5k times · Source

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 sproc 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

Charles Bretana picture Charles Bretana · Sep 29, 2009

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 questions

Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
Execute Stored Procedure from a Function

I know this has been asked to death, and I know why SQL Server doesn't let you do it. But is there any workaround for this, other than using Extended Stored Procedures? And please don't tell me to convert my …

Read More
SP taking 15 minutes, but the same query when executed returns results in 1-2 minutes

So basically I have this relatively long stored procedure. The basic execution flow is that it SELECTS INTO some data into temp tables declared with he # sign and then runs a cursor through these tables a generate a 'running total' …

Read More