Fetch direction modes

Emulation context

SQL Server cursors support a range of fetch direction modes, including NEXT, PRIOR, FIRST, LAST, ABSOLUTE, and RELATIVE. These modes control how the cursor navigates through the result set, allowing both forward and backward traversal, random access, and offset-based jumps.

In contrast, Microsoft Fabric Warehouse has no native cursor support and no equivalent low-level row pointer mechanism. This makes direct emulation of directional fetching particularly challenging, especially for modes that depend on dynamic or relative positioning (such as PRIOR, RELATIVE, or ABSOLUTE).

---

Emulation strategy

SQL Tran translates fetch direction modes as follows:

• FETCH NEXT and FETCH PRIOR inside loops: Translated into a WHILE loop over a pre-materialized temporary table with a numeric row counter (e.g., @CurrentRow).

  • NEXT increments the counter.

  • PRIOR decrements the counter.

  However, this mapping has limitations:

  • The loop typically starts at row 1, which makes PRIOR ineffective unless correctly pre-positioned (often missed in translation).

  • Boundary handling (e.g., underflow to row 0) is not always robust.

• FETCH FIRST: Silently mapped to RowNum = 1 in the temp table. No diagnostic or fix comment is inserted. The intent of explicitly fetching the first row is preserved, but the keyword is dropped in translation.

• FETCH RELATIVE (with offset): Currently unsupported. SQL Tran emits an explicit error comment: -- ERROR: [TARGET] Currently can't handle CURSOR with FETCH MODE other than PRIOR or NEXT in WHILE block No fallback or partial translation is provided.

• FETCH ABSOLUTE: Ignored silently. The translated loop always starts at row 1, even if the original code specifies an absolute position like row 5. There is no diagnostic or fix comment warning about this semantic loss.

This means that while basic directional movement (NEXT, PRIOR) is partially supported, positional or offset-based navigation (ABSOLUTE, RELATIVE) is effectively not emulated. Users should manually review and adjust such cases after translation.

---

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 FIRST FROM person_cursor INTO @BusinessEntityID, @LastName;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Placeholder for logic
        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 > 0
    BEGIN
        -- Placeholder for logic
        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

Initial fetch cursor emulation in SQL Tran

---

Important notes

• Discrepancy in PRIOR behavior: The translated loop initializes the counter at row 1 and decrements with PRIOR, which results in processing the first row once, then exiting when the counter reaches zero. This differs from SQL Server, where FETCH PRIOR without prior positioning results in no rows processed.

• Limited loop support: Only FETCH NEXT and FETCH PRIOR are supported inside loops. Any other fetch mode used in the loop body (FETCH RELATIVE, FETCH ABSOLUTE, etc.) will halt translation and emit an error comment in the output.

• Initial RELATIVE and ABSOLUTE positioning ignored: When FETCH RELATIVE or FETCH ABSOLUTE is used outside the loop for initial positioning, SQL Tran silently ignores these offsets, and the translated loop always starts from row 1.

• Stacked initial FETCH statements: SQL Tran translates multiple FETCH statements before the loop (e.g., combinations of FETCH NEXT, FETCH PRIOR) by sequentially adjusting the internal row counter and fetching the corresponding rows. This behavior is deterministic and matches the sequence of the source SQL, but no diagnostics are added. Manual review is advised to ensure the final pre-loop position aligns with business logic.

• Silent dropping of FIRST: FETCH FIRST is effectively translated as RowNum = 1 without explicit acknowledgment. There is no diagnostic or annotation in the output indicating that this specific intent was recognized.

• Manual review strongly recommended: Any use of advanced fetch directions should be carefully reviewed after translation, as the current SQL Tran output either ignores, partially mishandles, or blocks these cases. This could lead to incorrect or incomplete Fabric logic.