SQL Server Performance

Using While loop instead of Cursor.. How?

Discussion in 'General Developer Questions' started by sonnysingh, Aug 31, 2006.

  1. sonnysingh Member

    Hi folks

    I want to use while loop instead of cursor.. any refer to this method or scripts to understand it????

    Thanks in advance..
    sonnysingh, Aug 31, 2006
    #2

  2. satya Moderator

    http://www.sqlteam.com/item.asp?ItemID=5761 fyi.

    Satya SKJ
    Microsoft SQL Server MVP
    Contributing Editor & Forums Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing.
    satya, Aug 31, 2006
    #2

  3. mmarovic Active Member

    There is no advantage of using while loop over the cursor if you are still processing one row at time.
    mmarovic, Sep 2, 2006
    #2

  4. FrankKalis Moderator

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by mmarovic</i><br /><br />There is no advantage of using while loop over the cursor if you are still processing one row at time.<br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br />Well, there is one advantage though. You can say with a clear conscience that you don't use cursors. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
    FrankKalis, Sep 4, 2006
    #2

  5. ranjitjain New Member

    ranjitjain, Sep 4, 2006
    #2

  6. Adriaan New Member

    Using a WHILE loop means you can do processing in smaller batches, without using a cursor, which can be helpful when a single large batch operation would slow down the whole system.

    [Edit:]
    SET ROWCOUNT 100
    WHILE EXISTS (SELECT * FROM table WHERE column <> value and condition)
    BEGIN
    UPDATE table SET column = value WHERE column <> value and condition
    END
    SET ROWCOUNT 0
    Adriaan, Sep 4, 2006
    #2

Share This Page