Data modification cursors

Emulation context

In SQL Server, cursors can be used not only for reading data but also for performing per-row modifications using statements like UPDATE ... WHERE CURRENT OF or DELETE ... WHERE CURRENT OF. These positional updates allow direct modification of the row currently pointed to by the cursor, without explicitly specifying a WHERE condition on key columns.

However, Microsoft Fabric Warehouse does not support cursors or positional update mechanisms. Fabric’s distributed execution model requires deterministic, set-based queries, and it lacks constructs like WHERE CURRENT OF or cursor-based row targeting.

This creates a critical challenge for migrating data modification cursors, as Fabric-compatible translation cannot directly reproduce the positional semantics of these updates.

---

Emulation strategy

SQL Tran translates data modification cursors as follows:

• It rewrites the cursor loop using its standard emulation pattern:

  • Materializes the result set into a temporary table (#person_cursor) with explicit row numbers.

  • Drives the iteration through a numeric loop with @CurrentRow_person_cursor.

• It preserves the original UPDATE ... WHERE CURRENT OF or DELETE ... WHERE CURRENT OF statements unchanged in the translated output.

However:

• Since Fabric has no native support for WHERE CURRENT OF, these statements become non-functional or invalid after translation.

• SQL Tran does not automatically rewrite these updates to use explicit WHERE clauses based on primary keys or unique identifiers.

• The translated code requires manual intervention by the user to replace positional updates with explicit row-matching conditions (e.g., WHERE Person.Person.BusinessEntityID = @BusinessEntityID).

If the cursor’s result set lacks a reliable unique identifier, the user must refactor the logic to safely reconstruct the target row.

---

Code example

SQL Server:

CREATE PROCEDURE dbo.ProcessPeople
AS
BEGIN
    DECLARE @BusinessEntityID INT, @LastName NVARCHAR(50);
    DECLARE person_cursor CURSOR SCROLL FOR
    SELECT BusinessEntityID, LastName FROM Person.Person;
    OPEN person_cursor;
    FETCH NEXT FROM person_cursor INTO @BusinessEntityID, @LastName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE Person.Person
        SET LastName = LastName + '_updated'
        WHERE CURRENT OF person_cursor;
        FETCH NEXT FROM person_cursor INTO @BusinessEntityID, @LastName;
    END;
    CLOSE person_cursor;
    DEALLOCATE person_cursor;
END

Fabric Warehouse (generated by SQL Tran):

CREATE PROCEDURE dbo.ProcessPeople
AS
BEGIN
    DECLARE @BusinessEntityID INT, @LastName NVARCHAR(50);
    DECLARE @RowCount_person_cursor INT;
DECLARE @CurrentRow_person_cursor INT = 1;
CREATE TABLE #person_cursor (
	RowNum INT, BusinessEntityID INT, LastName NVARCHAR(50)
)INSERT
	INTO #person_cursor (RowNum, BusinessEntityID, LastName)
	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
		BusinessEntityID,
		LastName
	FROM
		Person.Person;
SELECT @RowCount_person_cursor = COUNT(*)
FROM
	#person_cursor;
    
    SELECT @BusinessEntityID = BusinessEntityID,
	@LastName = LastName
FROM
	#person_cursor
WHERE
	RowNum = @CurrentRow_person_cursor;
    WHILE @CurrentRow_person_cursor <= @RowCount_person_cursor
    BEGIN
        UPDATE Person.Person
        SET LastName = LastName + '_updated'
        WHERE CURRENT OF person_cursor;
        SET @CurrentRow_person_cursor = @CurrentRow_person_cursor + 1;
SELECT @BusinessEntityID = BusinessEntityID,
	@LastName = LastName
FROM
	#person_cursor
WHERE
	RowNum = @CurrentRow_person_cursor;
    END;
    
    DROP TABLE IF EXISTS #person_cursor;
END

Data modification cursor emulation in SQL Tran

---

Important notes

• No functional WHERE CURRENT OF: SQL Tran preserves WHERE CURRENT OF statements in the translated code, but Fabric does not support this syntax. These lines remain in the output but must be manually rewritten.

• Manual rewriting required: Users must replace positional updates (e.g., WHERE CURRENT OF person_cursor) with explicit WHERE clauses using unique identifiers (e.g., WHERE Person.Person.BusinessEntityID = @BusinessEntityID) to ensure correct row targeting.

• No automatic key inference: SQL Tran does not analyze the cursor result set to identify primary keys or construct safe update conditions. The responsibility for ensuring correct targeting lies entirely with the user.

• No diagnostic warnings: The translated output includes no comments or warnings about dropped or unsupported update semantics. Manual review is essential to avoid silent logic failures.

• Potential semantic mismatch: If the original cursor relies on positional targeting without a key-based match, the translated Fabric code will not preserve the same behavior. A redesign of the logic may be needed.