SQL Server Performance

Looping thru a table

Discussion in 'General Developer Questions' started by eramgarden, Jun 12, 2006.

  1. eramgarden New Member

    I've created a table with a identity column RowID. This code should loop thru the table but I only get one iteration. Not sure what I'm missing. Dont want to use cursors.

    The table has 56 rows.




    declare @rowid as int
    SELECT TOP 1 @rowid=rowid from cCollInfo
    where Processed=0 ORDER BY rowid

    print @rowid --Prints 1

    SELECT TOP 1 @rowid=rowid
    from cCollInfo
    WHERE rowid>@rowid and Processed=0 ORDER BY rowid

    print @rowid --Prints 2. But this is outside the loop


  2. eramgarden New Member

    never mind, found my answer

    SELECT TOP 1 @NextId = id FROMTempTable ORDER BY Id
    WHILE @@ROWCOUNT>0
    BEGIN
    EXEC SomeStoredProc @NextId
    SELECT TOP 1 @NextId = id FROMTempTable WHERE Id>@NextId ORDER BY Id
    END

Share This Page