SQL Server Performance Forum – Threads Archive

Looping thru a table

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

---

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

---

]]>