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?

Does this answer your question? [How to SELECT FROM stored procedure](https://stackoverflow.com/questions/1492411/how-to-select-from-stored-procedure) — Stu, Oct 04 '22 at 14:55

score 4 · Accepted Answer · answered Oct 04 '22 at 14:46

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;

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

1 Answers1