CODEWITHSUNDEEP
sqlsql-server-2005common-table-expression
user167698
user167698

Reputation: 1863

Call a Stored procedure in SQL CTE

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

Upvotes: 29

Views: 37448

Answers (2)

Sagar Dev Timilsina
Sagar Dev Timilsina

Reputation: 1390

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

Upvotes: 4

gbn
gbn

Reputation: 432331

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

Upvotes: 35

Related Questions