Menu
Are you allowed to exec stored procedures within a SQL CTE statement? I'm a bit new to sql cte queries...
894
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.
You can call an SQL stored procedure with the execute, open, or get statement; in each case, you use the #sql directive. A stored procedure is a set of instructions for a database, like a function in EGL.
Using the CTE –After you define your WITH clause with the CTEs, you can then reference the CTEs as you would refer any other table. However, you can refer a CTE only within the execution scope of the statement that immediately follows the WITH clause.
In Object Explorer, connect to an instance of the SQL Server Database Engine, expand that instance, and then expand Databases. Expand the database that you want, expand Programmability, and then expand Stored Procedures. Right-click the user-defined stored procedure that you want and select Execute Stored Procedure.
No, sorry. SELECTs statments only
If you need to use stored proc output (result set), then it'd be a temp table
CREATE TABLE #foo (bar int...)
INSERT #foo (bar, ...)
EXEC myStoredProc @param1...
-- more code using #foo
You can also use table variable :
DECLARE @tbl TABLE(id int ,name varchar(500) ,...)
INSERT INTO @tbl
EXEC myprocedure @param ..
with cte as (
SELECT * FROM @tbl
)
select * from cte
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
