Say I want to loop through the values in the table xval which is defined as:
CREATE TABLE xval (x INT NULL);
INSERT INTO xval(x)
VALUES (13),(1),(42),(9),(-2),(14);
GO
right now I have the following:
DECLARE @x INT
DECLARE pointer CURSOR FOR (SELECT x FROM xval)
OPEN pointer
FETCH NEXT FROM pointer INTO @x
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @x
FETCH NEXT FROM pointer
END
CLOSE pointer
DEALLOCATE pointer
When I run this, SSMS gives me multiple batches in the results window and in each batch it selects one value from the xval table. But these batches only include the values 1,42,9,-2,14 and an empty batch at the end. In the messages window, however, I get the following:
13
(1 row affected)
13
(1 row affected)
13
(1 row affected)
13
(1 row affected)
13
(1 row affected)
13
(0 rows affected)
Completion time: 2020-08-30T21:54:14.7480477+01:00
I would like to know what's going on: 1. Why am I not getting 13 in the first batch? 2. Why are the multiple batches in the first place? 3. Why am I getting only 13 in the messages...
I realised that I need to always fetch into the variable I have declared.
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @x
FETCH NEXT FROM pointer **INTO @x**
END
Since I didn't fetch the value into @x in the loop, it keeps printing out @x which was initially set to 13 (FETCH NEXT FROM pointer INTO @x on line 4). So every time we loop around it prints 13. It also fetches the next value from xval and so the results window shows what it's fetching. Since, it has already fetched 13 into x, the pointer moves onto the next values. So you don't see 13 in the results window.
The empty set at the end is because it continues to fetch the next value from the cursor before the while loop check kicks in (@@FETCH_STATUS = 0), even though the list has been depleted. Which is why it's empty because it tries to look for the next value but there isn't any value there.