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?
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.
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
But I'm not sure what that accomplishes over SELECT ABC FROM #TMP_ABC;
.