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
orDELETE ... 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

Important notes
No functional
WHERE CURRENT OF
: SQL Tran preservesWHERE 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 explicitWHERE
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.
Last updated