Can I treat the results of a stored procedure like

Question

Is it possible to do something like this in sql server 2005?

WITH tmpTable AS (EXEC spWhatever)

Or any other way I can query the data returned from the sp? Thanks!!!

Answer 1

as far as i know you can not. But you can try using User Defined Functions (UDF) instead of SP, if you do that you can you use it like a table.

Answer 2

Temp table:

CREATE TABLE #foo (col1 int, col2 char(10), ...)

INSERT #foo 
EXEC myproc

Or loopback (not sure if this still works). Edit: Could be OPENROWSET as per SQLMenace's answer

SELECT * FROM OPENQUERY (MyServername, 'USE MyDB EXEC myproc')

Answer 3

only with a loopback query if you don't first want to create the table, see here: Store The Output Of A Stored Procedure In A Table Without Creating A Table

example

      SELECT * INTO #TempSpWho
            FROM OPENROWSET ('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;',
    'set fmtonly off exec master.dbo.sp_who')

SELECT * FROM #TempSpWho