Menu
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'.
314
This means that CTE is valid only to the scope of the query. However, you can write a CTE inside a stored procedure or User Defined Functions (UDFs) or triggers or views. However, you cannot implement CTEs inside indexed views.
According to the CTE documentation, Common Table Expression is a temporary result set or a table in which we can do CREATE, UPDATE, DELETE but only within that scope. That is, if we create the CTE in a Stored Procedure, we can't use it in another Stored Procedure.
Here is an example of how to call a stored procedure inside another stored procedure. This is also known as nested stored procedures in SQL Server. Step 1: Create two simple stored procedure to insert some data into two different tables. both accept four parameters to insert the data.
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.
184
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
