Why is the same value being return in this SQL While Loop?

January 20, 2022

I am using Microsoft SQL Server, and attempting to insert some data into a temporary table. I then want to use a while loop to loop through each row in the temporary table. I do no want to use a cursor.

Please see the query below:

-- Create Table

DROP TABLE IF EXISTS #TMP_ABC

CREATE TABLE #TMP_ABC

(
  [ABC] [varchar](3) NULL,
)
 
-- Insert Values
INSERT INTO [#TMP_ABC] VALUES ('AAA')
INSERT INTO [#TMP_ABC] VALUES ('BBB')
INSERT INTO [#TMP_ABC] VALUES ('CCC')
INSERT INTO [#TMP_ABC] VALUES ('DDD')
INSERT INTO [#TMP_ABC] VALUES ('EEE')
INSERT INTO [#TMP_ABC] VALUES ('FFF')
 
-- Display values

DECLARE @count INT
DECLARE @row INT
SET @row = 1;

DECLARE @ABC varchar(3)

SET @count = (SELECT COUNT(ABC) FROM #TMP_ABC)

WHILE (@row <= @count) BEGIN
    SELECT @ABC = ABC FROM #TMP_ABC
    PRINT @ABC
    SET @row += 1
END

Here is what is returned from the query:

(1 row affected)
FFF
FFF
FFF
FFF
FFF
FFF

I was hoping for the following to be returned instead:

(1 row affected)
AAA
BBB
CCC
DDD
EEE
FFF

Please can somebody ‘kindly’ show me the error in my ways, and how to achieve this?

>Solution :

The issue happens because SQL Server doesn’t associate @row with a row in the table (the correlation is obvious to you, but SQL Server isn’t human).

As you loop through the numbers 1 -> @count, it is running the same SELECT @ABC = ABC FROM #TMP_ABC over and over again. There is no WHERE clause and no TOP so SQL Server is just reading the whole table every time, and setting the variable equal to the last ABC value it read.

Instead, you should use a cursor (if you need to loop at all; usually you don’t, per @Larnu’s comment). You have have read some misinformation somewhere that cursors are bad and that while loops are not cursors, but these are both false.

Bad Habits to Kick : Thinking a WHILE loop isn’t a CURSOR
What impact can different cursor options have?
Follow-up on cursor options
Overlooked T-SQL Gems (see why using a local variable for a cursor is even better than )

If you do in fact need to loop for some reason, here’s a rewrite:

DROP TABLE IF EXISTS #TMP_ABC;

CREATE TABLE #TMP_ABC(ABC varchar(3) NULL);

INSERT INTO #TMP_ABC(ABC) VALUES 
  ('AAA'),('BBB'),('CCC'),('DDD'),('EEE'),('FFF');
  
DECLARE @ABC varchar(3), @c cursor;
    
SET @c = cursor FOR SELECT ABC FROM #TMP_ABC;

OPEN @c;

FETCH NEXT FROM @c INTO @ABC;

WHILE @@FETCH_STATUS = 0
BEGIN
  PRINT @ABC;
  FETCH NEXT FROM @c INTO @ABC;
END

Output: