Will the source dataset for a cursor update on each iteration if within the cursor loop I'm updating one of the tables that dataset references?

Question

In this example:

DECLARE @KeyField INT, @ValueField INT

DECLARE CursorName CURSOR FAST_FORWARD
FOR SELECT T1.KeyField, T1.ValueField
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
    ON T1.ValueField = T2.ValueField
WHERE T2.KeyField IS NULL -- Filter out any records that have the same value in T2 as T1

OPEN CursorName FETCH NEXT FROM CursorName
INTO @KeyField, @ValueField

WHILE (@@FETCH_STATUS = 0)
BEGIN

    INSERT INTO Table2 (KeyField, ValueField)
    SELECT @KeyField, @ValueField

    FETCH NEXT FROM CursorName
    INTO @KeyField, @ValueField

END

CLOSE CursorName
DEALLOCATE CursorName;

(I forgot how much I "loved" cursors.)

Would the update to Table2 inside the cursor loop body, cause that value to be filtered out from the dataset on the next iteration of the loop?...in other words does updating Table2 modify the dataset that uses Table2 for the original cursor definition.

Answer 1

in other words does updating Table2 modify the dataset that uses Table2 for the original cursor definition.

You are inserting data into table2 and these data changes could be visible unless you specify the STATIC keyword.

STATIC

Specifies that the cursor always displays the result set as it was when the cursor was first opened, and makes a temporary copy of the data to be used by the cursor. All requests to the cursor are answered from this temporary table in tempdb

There are other implications to what data is visible to the cursor depending on the declaration of the cursor.

Keywords such as KEYSET, DYNAMIC, FAST_FORWARD,FORWARD_ONLY impact this behaviour.

An example in the documentation on FORWARD_ONLY

...All insert, update, and delete statements made by the current user (or committed by other users) that affect rows in the result set are visible as the rows are fetched. Because the cursor cannot be scrolled backward, however, changes made to rows in the database after the row was fetched are not visible through the cursor...

When using FORWARD_ONLY, data could be visible depending on what row the cursor is located at.

---

However, in your example, due to the filter used:

WHERE T2.KeyField IS NULL -- Filter out any records that have the same value in T2 as T1

Even if new rows are added, the inserted data will be filtered out as non nulls are inserted into Table2.

You could test this adaptation of your query to create an endless looping cursor.

DROP TABLE dbo.Table1;
DROP TABLE dbo.Table2;
CREATE TABLE dbo.Table1(KeyField int,ValueField int);

INSERT INTO dbo.Table1
SELECT TOP(10) ROW_NUMBER() OVER( ORDER BY(SELECT NULL)),
ROW_NUMBER() OVER( ORDER BY(SELECT NULL))
FROM MASTER..spt_values;

CREATE TABLE dbo.Table2(KeyField int,ValueField int)
INSERT INTO dbo.Table2
SELECT TOP(10) ROW_NUMBER() OVER( ORDER BY(SELECT NULL)),
ROW_NUMBER() OVER( ORDER BY(SELECT NULL))
FROM MASTER..spt_values;

DECLARE @KeyField INT, @ValueField INT;
DECLARE @counter int = 0;

DECLARE CursorName CURSOR 
FOR SELECT T1.KeyField, T1.ValueField
FROM Table1 AS T1
LEFT JOIN Table2 AS T2
    ON T1.ValueField = T2.ValueField
WHERE T2.KeyField IS NOT  NULL; -- CHANGED TO NOT NULL

OPEN CursorName FETCH NEXT FROM CursorName
INTO @KeyField, @ValueField

WHILE (@@FETCH_STATUS = 0)
BEGIN

    INSERT INTO Table2 (KeyField, ValueField)
    SELECT @KeyField, @ValueField
        SELECT @KeyField, @ValueField
    FETCH NEXT FROM CursorName
    INTO @KeyField, @ValueField
    SET @counter += 1
END

CLOSE CursorName
DEALLOCATE CursorName;


SELECT @counter;