Using CTE with while loop in SQL server 2005

Question

I want to use CTE with while loop. Is it possible

my code:

; with myCTE(a,b)
(
 select .,. from abc
)
while exist (select * from mycet) -- causing issue

Please suggest some solution.

Regards, Anuprita

not sure what you want to do but, a while loop is not the right way to think about it. SQL is a set based langauge. — Jodrell, Oct 15 '13 at 14:16
SQL Server (title) or MySQL (tag)? For SQL Server, you can't combine these features. — Damien_The_Unbeliever, Oct 15 '13 at 14:17
just move the CTE into a derived table. however it will have to rerun the query for each iteration. I'd try to avoid the loop, if you can't I'd store the results in a table variable and loop over that: http://stackoverflow.com/q/1578198/65223 — KM., Oct 15 '13 at 14:34
You can populate a temp table from a CTE, then use the temp table for your loop, see this example in SQLFiddle http://sqlfiddle.com/#!3/634db4/3. — Question3CPO, Oct 15 '13 at 14:36

score 2 · Answer 1 · answered Oct 15 '13 at 14:17

A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.

But you should use a set based approach instead of loops anyway. Apart from that, it is not clear what your query should return.

1 Answers1