Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2002
    Posts
    13

    Unanswered: 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,
    Pedro Henrique Nunes
    MCP-MCP+I-MCSENT4-MCSEW2K
    Sao Paulo - Brasil

  2. #2
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    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

    Code:
    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

  3. #3
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •