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

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?

CodePudding user response：

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 the regular type you probably use)

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

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

INSERT INTO #TMP_ABC(ABC) VALUES 
  ('AAA'),('BBB'),('CCC'),('DDD'),('EEE'),('FFF');
  
DECLARE @ABC varchar(3), @c cursor;
    
SET @c = cursor LOCAL FAST_FORWARD
    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:

AAA
BBB
CCC
DDD
EEE
FFF

Example db<>fiddle

But I'm not sure what that accomplishes over SELECT ABC FROM #TMP_ABC;.

CodePudding user response：

use ROW_NUMBER() function and pass the value through a variable in WHERE clause in the loop.

--Create another Temp table
CREATE table #Tem_abc (r int , abc varchar(3));

--Add Row number

insert into #Tem_abc
select ROW_NUMBER() over( order by ABC) as r,*
from #TMP_ABC;

--Use the loop
DECLARE @count INT;
DECLARE @row INT;
SET @row = 1;
DECLARE @ABC varchar(3);
SET @count = (SELECT COUNT(ABC) FROM #TMP_ABC);
--SET @count = (SELECT max(r) FROM #Tem_abc);

WHILE (@row <= @count) BEGIN
    
    SELECT @ABC = ABC FROM #Tem_abc where @row = r;
     PRINT @ABC;
    SET @row  = 1;
END