Do While in SQL !

Hi Everybody,

I wanna know how to do a "Do While" inner SQL Query Analyser or in a Stored Procedure.
Usually I have to make some programs in VB to go until End of File and in each row depending of course of a condition I make a INSERT/UPDATE/DELETE.

The VB Code is such as:

strSQL = "SELECT id FROM table ORDER BY id"
Set objRs = Conn.Execute(strSQL)
If not objRs.EOF Then
Do Until objRs.EOF
strSQL = "UPDATE Table SET field = 'x' WHERE id = '"& objRs(0) &"'"
Conn.Execute(strSQL)
objRs.MoveNext
Loop
End If

How it Works in SQL? Using While?

Tanks,Using CURSOR allows you to loop, just like an ADO.Recordset. Each FETCH sets a global variable called @.@.fetch_status, which is your test condition (EOF).

If the table you are updating is the table that the cursor is on look at using the option WHERE CURRENT OF under UPDATE on Books On Line

declare @.id int

declare tb_cur cursor
for
select id
from table
order by id

open tb_cur

fetch next from tb_cur into @.id

while @.@.fetch_status = 0 begin
UPDATE Table SET field = 'x'
WHERE id = @.id

fetch next from tb_cur into @.id
end

close tb_cur
deallocate tb_cur|||You are dealing with a relational database and sql. Trying to use procedural code will get you into trouble as well as giving extremely poor performance. Use of cursors usully indicates a poor design or lack of sql knowledge - you will find that they are never needed on sql server.

In your case here

strSQL = "SELECT id FROM table ORDER BY id"
Set objRs = Conn.Execute(strSQL)
If not objRs.EOF Then
Do Until objRs.EOF
strSQL = "UPDATE Table SET field = 'x' WHERE id = '"& objRs(0) &"'"
Conn.Execute(strSQL)
objRs.MoveNext
Loop
End If

update tbl
set field = 'x'

look at the case statement and where clause for a conditional update

if you want to loop through each row

declare @.id int, @.maxis int
select @.id = 0, @.maxid = max(id) from table
while @.id < @.maxid
begin
select @.id = min(id) from table where id > @.id
update table set field = 'x' where id = @.id
end

as I say don't try to use this on large tables or those which are multiuser.