CODEWITHSUNDEEP
sql-serverstored-procedurescommon-table-expression
tjcinnamon
tjcinnamon

Reputation: 375

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'.

Upvotes: 8

Views: 9878

Answers (2)

Raghvendra Rajak
Raghvendra Rajak

Reputation: 1

But using a table variable "@myTbleVarible" needs to be defined first with the same schema (column order and data type same as returning from stored procedure result set) only then it can be used to receive the result from the stored procedure.

Upvotes: 0

Tab Alleman
Tab Alleman

Reputation: 31785

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.

Upvotes: 9

Related Questions