Search code examples
sql-serverstored-procedurescommon-table-expression

Return Stored Proc Results in CTE

Is it possible for me to call a stored proc into a CTE. I have a login to our reporting DB that is only RO. I have write access to our UAT but would like to query live data.

So can I use a stored proc in a CTE?

with clientOwes as (
  exec des_Batch_GetApplicationClientOwesList
)     
select a.des_applicationnumber 
from des_heapplicationset a 
where a.des_heapplicationid in (select applicationid from clientowes)

result was: Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'exec'.

Solution

  • Answer adapted from dialogue in comments:

    You can use a stored procedure to populate a table variable, which Read Only access does allow you to create. You won't need to use OpenRowSet to populate it either. Just do:

    INSERT INTO @MyTableVariable
EXEC MyStoredProcedure

    I do this in a lot of places myself where I need to treat Stored Proc results as a table that I can JOIN or UNION with other tables.