Nested cursors

Emulation context

In SQL Server, nested cursors allow one cursor to operate inside the loop of another, enabling multi-level, row-by-row processing over related datasets. For example, an outer cursor might iterate over customers, while an inner cursor loops over each customer’s orders.

This pattern is often used in:

• Parent-child data processing (e.g., customers and orders)
• Hierarchical data traversal
• Complex reporting with per-parent aggregations

However, Microsoft Fabric Warehouse lacks native cursor support and does not provide nested or stacked iterator constructs. Its distributed execution model focuses on set-based, parallelizable operations rather than nested procedural loops, making direct translation of nested cursors particularly challenging.

SQL Tran must transform these nested patterns into a compatible, deterministic structure using static materialization and iterative control flow.

Emulation strategy

SQL Tran emulates nested cursor patterns using layered translation:

• Each cursor (outer and inner) is translated into its own pre-materialized temporary table (e.g., #outer_cursor, #inner_cursor) with an explicit row number (ROW_NUMBER()), snapshotting the data at the time of translation.
• The outer cursor loop drives the top-level iteration using an index variable (e.g., @CurrentRow_outer_cursor), while the inner cursor loop operates inside, using its own independent index (e.g., @CurrentRow_inner_cursor).
• The temp tables are created and dropped at their respective scopes.
• Data dependencies between outer and inner loops (such as passing a parent key from the outer cursor into the inner cursor’s WHERE clause) are preserved by translating them into parameterized subqueries feeding the inner temp table.

Key transformation behaviors include:

• Scope isolation: Each temp table and loop variable is maintained separately to avoid cross-interference.
• Repeated materialization: The inner temp table is re-created for each outer loop iteration, matching the per-parent filtering from the original SQL Server pattern.
• Sequential control: Although Fabric is optimized for set-based operations, SQL Tran preserves the nested loop logic deterministically, ensuring row-by-row consistency.

This approach makes nested cursor emulation feasible — but introduces performance and resource considerations, as materializing and iterating over large inner datasets repeatedly can be expensive.

Code example

SQL Server:

CREATE PROCEDURE dbo.ProcessPersonEmails
AS
BEGIN
    DECLARE @BusinessEntityID INT;
    DECLARE outer_cursor CURSOR FOR
	SELECT BusinessEntityID FROM Person.Person;
    OPEN outer_cursor;
    FETCH NEXT FROM outer_cursor INTO @BusinessEntityID;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        DECLARE @Email NVARCHAR(50);
		DECLARE inner_cursor CURSOR FOR
		SELECT EmailAddress FROM Person.EmailAddress WHERE BusinessEntityID = @BusinessEntityID;
		OPEN inner_cursor;
		FETCH NEXT FROM inner_cursor INTO @Email;
		WHILE @@FETCH_STATUS = 0
		BEGIN
			-- Placeholder for logic
			FETCH NEXT FROM inner_cursor INTO @Email;
		END;
		CLOSE inner_cursor;
		DEALLOCATE inner_cursor;
		FETCH NEXT FROM outer_cursor INTO @BusinessEntityID;
    END;
	CLOSE outer_cursor;
	DEALLOCATE outer_cursor;
END

Fabric Warehouse (generated by SQL Tran):

CREATE PROCEDURE dbo.ProcessPersonEmails
AS
BEGIN
    DECLARE @BusinessEntityID INT;
    DECLARE @RowCount_outer_cursor INT;
DECLARE @CurrentRow_outer_cursor INT = 1;
CREATE TABLE #outer_cursor (
	RowNum INT, BusinessEntityID INT
)INSERT
	INTO #outer_cursor (RowNum, BusinessEntityID)
	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
		BusinessEntityID
	FROM
		Person.Person;
SELECT @RowCount_outer_cursor = COUNT(*)
FROM
	#outer_cursor;
    
    SELECT @BusinessEntityID = BusinessEntityID
FROM
	#outer_cursor
WHERE
	RowNum = @CurrentRow_outer_cursor;
    WHILE @CurrentRow_outer_cursor <= @RowCount_outer_cursor
    BEGIN
        DECLARE @Email NVARCHAR(50);
		DECLARE @RowCount_inner_cursor INT;
DECLARE @CurrentRow_inner_cursor INT = 1;
CREATE TABLE #inner_cursor (
	RowNum INT, EmailAddress NVARCHAR(50)
)INSERT
	INTO #inner_cursor (RowNum, EmailAddress)
	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
		EmailAddress
	FROM
		Person.EmailAddress
	WHERE
		BusinessEntityID = @BusinessEntityID;
SELECT @RowCount_inner_cursor = COUNT(*)
FROM
	#inner_cursor;
		
		SELECT @Email = EmailAddress
FROM
	#inner_cursor
WHERE
	RowNum = @CurrentRow_inner_cursor;
		WHILE @CurrentRow_inner_cursor <= @RowCount_inner_cursor
		BEGIN
			-- Placeholder for logic
			SET @CurrentRow_inner_cursor = @CurrentRow_inner_cursor + 1;
SELECT @Email = EmailAddress
FROM
	#inner_cursor
WHERE
	RowNum = @CurrentRow_inner_cursor;
		END;
		
		DROP TABLE IF EXISTS #inner_cursor;
		SET @CurrentRow_outer_cursor = @CurrentRow_outer_cursor + 1;
SELECT @BusinessEntityID = BusinessEntityID
FROM
	#outer_cursor
WHERE
	RowNum = @CurrentRow_outer_cursor;
    END;
	
	DROP TABLE IF EXISTS #outer_cursor;
END

Important notes

• Temp table layering: Each nested cursor introduces an additional temp table (#outer_cursor, #inner_cursor), increasing schema complexity and execution overhead.
• Repeated table creation: The inner cursor’s temp table is created and dropped on every outer iteration. While functionally correct, this can lead to performance bottlenecks, especially if the inner dataset is large.
• Variable isolation: Outer and inner cursor loop variables (e.g., @CurrentRow_outer_cursor and @CurrentRow_inner_cursor) are isolated but follow consistent naming patterns. Manual review is advised to ensure no accidental cross-scope contamination.
• No parallelization: While Fabric is a distributed platform, the translated nested loops execute serially, reflecting the original SQL Server procedural logic. This may negate some of Fabric’s set-based performance advantages.
• Manual review recommended: Nested cursor translations are structurally correct but may not be optimal for performance. Users should assess whether the logic can be rewritten as set-based queries or analytic patterns in Fabric for better scalability.