Can I process the results of a stored procedure, such as a table?

Question

Is it possible to do something similar in sql server 2005?

WITH tmpTable AS (EXEC spWhatever)

Or in any other way I can request data returned from sp? Thank!!!

Answer 1

Temp table:

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

INSERT #foo 
EXEC myproc

Or loopback (not sure if this still works). Edit: may be OPENROWSET according to SQLMenace answer

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

Answer 2

only with a loopback request, if you do not want to create a table first, see here: Save the output of a stored procedure to 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

Answer 3

, . (UDF) SP, , .