Switching from using a CURSOR to a WHILE loop will mean they are not processing by a row. This is not true.

I have seen cases where the CURSOR method is faster than the WHILE method and vica versa. But both methods utilise the row by row approach.

The following examples illustrates this myth. Both examples are processing row by row :

---Example 1 - CURSOR
create table #mytemp
(ID INT ,
avalue VARCHAR(100),
status INT
)
INSERT INTO #mytemp (ID,avalue,status) VALUES(1,'Mick Jagger',0)
INSERT INTO #mytemp (ID,avalue,status) VALUES(2,'Keith Richards',0)
INSERT INTO #mytemp (ID,avalue,status) VALUES(3,'Ron Wood',0)
declare @cnt INT,@tmpINT INT
SET @cnt = 1
DECLARE my_cursor CURSOR FOR
SELECT ID FROM #mytemp
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @tmpINT
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @cnt
UPDATE #mytemp SET status = 1 WHERE ID = @cnt
SET @cnt = @cnt + 1
FETCH NEXT FROM my_cursor
INTO @tmpINT;
END
CLOSE my_cursor;
DEALLOCATE my_cursor;
--Example 2 - WHILE loop
create table #mytemp
(ID INT ,
avalue VARCHAR(100),
status INT
)
INSERT INTO #mytemp (ID,avalue,status) VALUES(1,'Mick Jagger',0)
INSERT INTO #mytemp (ID,avalue,status) VALUES(2,'Keith Richards',0)
INSERT INTO #mytemp (ID,avalue,status) VALUES(3,'Ron Wood',0)
declare @cnt INT
SET @cnt = 1
WHILE (SELECT COUNT(*) FROM #mytemp WHERE status = 0) 0
BEGIN
UPDATE #mytemp SET status = 1 WHERE ID = @cnt
SET @cnt = @cnt + 1
END
SELECT * FROM #mytemp
DROP TABLE #mytemp

Author: Jack Vamvas