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..
  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.
  3. mmarovic Active Member

    There is no advantage of using while loop over the cursor if you are still processing one row at time.
  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>
  5. ranjitjain New Member

  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

Share This Page