Return Stored Proc Results in CTE

Question

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

You might be able to use OPENROWSET for this. See if this answer helps you: http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table — pmbAustin, Mar 10 '15 at 16:19
Why do you need a CTE? Are you calling the stored procedure with different inputs every time? You're going to execute that procedure once for each row with this approach. I understand your access problem but if you tell us more about your goal we might be able to help you with a better solution. — jtimperley, Mar 10 '15 at 16:59
@TabAlleman `with clientOwes as ( exec des_Batch_GetApplicationClientOwesList ) select a.des_applicationnumber from des_heapplicationset a where a.des_heapplicationid in (select applicationid from clientowes)` Incorrect syntax near the keyword 'exec' — tjcinnamon, Mar 10 '15 at 17:13
And? What happens when you execute that query? You might want to edit your original question and add the query you tried and the result you got. — Tab Alleman, Mar 10 '15 at 17:16
@jtimperley I am using a CTE because it's RO. I suppose I could use the stored proc directly in the WHERE using an IN (SELECT...) but I don't know if that works either. Is it possible to use a stored Proc in the WHERE statement of a SELECT query? — tjcinnamon, Mar 10 '15 at 17:16
You can use a stored proc to populate a table variable, which I think you can do with RO access. — Tab Alleman, Mar 10 '15 at 17:17
@TabAlleman That would solve the problem for sure. Do I have to do that with the OpenRowSet or is there another method to populate a table variable (with RO access)? — tjcinnamon, Mar 10 '15 at 17:19
No need for OpenRowSet, just INSERT INTO MyTableVariable EXEC MyStoredProc — Tab Alleman, Mar 10 '15 at 17:29
@TabAlleman I'll give that a try with my RO access. I don't have server access to change DB settings so we'll see how this works (i.e. is allowed) . — tjcinnamon, Mar 10 '15 at 17:59
Depending on how many rows you expect back, I'd suggest using a temptable over a table variable. — pmbAustin, Mar 10 '15 at 19:28
I'll research the difference between the two and see what limitations my environment has in store for me. Will report back with results. — tjcinnamon, Mar 11 '15 at 20:12
@TabAlleman how do I select your comment for an answer? Or do I copy and paste into the answer? — tjcinnamon, Mar 19 '15 at 15:24

score 9 · Accepted Answer · answered Mar 19 '15 at 16:26

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.

Return Stored Proc Results in CTE

1 Answers1