Can I use a stored procedure inside a with Statement in SQL Server?

Question

In SQL Server I can use the with statement to simplify queries like this:

with x as 
(
    select 1 as a
)
select * 
from x

But what if the query I want to work with is actually a stored procedure?

with x as 
(
     exec p_queryComplexSP 12345, 0, null,'D+0','D+1095','Hour','GMT', 1
)
select * 
from x

Fails: SQL Error [156] [S0001]: Incorrect syntax near the keyword 'exec'.

Is there a correct way to express this query?

Answer 1

You can't do this within a CTE, but you can by storing the results of the proc in a temp table or table variable outside of the CTE.

DECLARE @ProcTable TABLE (Col1 INT, Col2 INT, Col3 INT);
INSERT @ProcTable (Col1, Col2, Col3)
EXEC p_MyProc;

WITH x AS (SELECT Col1, Col2, Col3 FROM @ProcTable)
SELECT *
FROM x;