Call a Stored procedure in SQL CTE

sql sql-server-2005 common-table-expression
user167698 picture user167698 · Jun 13, 2011 · Viewed 22.9k times · Source

Are you allowed to exec stored procedures within a SQL CTE statement? I'm a bit new to sql cte queries...

Answer

gbn picture gbn · Jun 13, 2011

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

Related questions

Incorrect syntax near the keyword 'with'...previous statement must be terminated with a semicolon

Im using SQL Server 2005 . I have 2 WITH Clauses in my stored procedure WITH SomeClause1 AS ( SELECT .... ) WITH SomeClause2 AS ( SELECT .... ) But the error occurs Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an …

Read More
How can I use if statement after a CTE (SQL Server 2005)

Last night I was writing a simple T-SQL program something like this DECLARE @ROLEID AS INT SELECT @ROLEID = [ROLE ID] FROM TBLROLE ;WITH CTE AS ( SELECT * FROM SOMETABLE ) IF (@ROLEID = 1) BEGIN //SOMECODE END ELSE IF(@ROLEID = 2) BEGIN //SOMECODE END ELSE …

Read More
How we can use CTE in subquery in sql server?

How we can use CTE in subquery in sql server? like .. select id (i want to use CTE here), name from table_name

Read More