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
WHEREclause) 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;
ENDFabric 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_cursorand@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.
Last updated