Many times I need to loop on data in sql server. A cursor is not the best performing most times. The main idea is to add a processed bit field to your temp table.
Technorati Tags:
sql,
loop
Here is how a loop thru data:
DECLARE @tblData table(rowid int,activity varchar(50), startTime time, endTime time, processed bit)
insert INTO @tblData VALUES (1,'Act. A','8:00','8:15',0)
insert INTO @tblData VALUES (2,'Act. B','8:15','8:45',0)
insert INTO @tblData VALUES (3,'Act. C','8:00','9:45',0)
insert INTO @tblData VALUES (4,'Act. D','8:15','10:45',0)
Declare @dID Int
WHILE EXISTS (SELECT * FROM @tblData WHERE Processed = 0)
BEGIN
--get current row
SELECT @dID = MIN(rowid) FROM @tblData WHERE Processed = 0
UPDATE @tblData SET Processed = 1 WHERE rowid = @dID
END
SELECT * from @tblData