t-sql looping without cursor

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: ,

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

Add comment