SQL Tran Docs
  • Overview
    • About
    • Getting started
    • Object lifecycle
    • Why is there no AI inside?
    • Performance considerations
    • Security considerations
    • Assessment
    • Unlocking projects
    • Interface walk-through
    • Translation scenarios
    • Prerequisites for Azure Marketplace deployment
  • Emulation Scenarios
    • Emulation in SQL Tran
    • SQL Server to Fabric Warehouse
      • Data types
      • Case sensitivity
      • Cursors
        • Basic cursor loop
        • Cursor types
        • Fetch direction modes
        • Cursors in control flow
        • Nested cursors
        • Data modification cursors
        • Multiple cursors
        • Subqueries and filtering
      • Named procedure parameters
      • Result set limiting
      • MERGE statements
      • Computed columns
      • External tables
      • Materialized views
      • Identity columns
      • Unsupported system objects
    • Synapse Analytics to Fabric Warehouse
      • Emulations
      • Limitations
    • SQL Server to Synapse Analytics
    • Oracle to PostgreSQL
  • Project wizard
    • Source database
    • Target database
    • Wrapping up
  • Projects
    • Project list
    • Overview
    • Workspace
    • Reports
    • Tests
    • Scratch pad
    • Settings
      • Project name
      • Mapping
      • Database connections
    • Navigation
    • Object complexity
    • Static analysis
    • Translation errors
    • Exporting and importing projects
  • Workspace
    • Object tree
    • Data lineage
    • Code
    • Actions
      • Overriding source
      • Overriding target
      • Ignoring objects
  • Tests
    • Workflow
    • Configure SQL Tran
    • Connecting to databases
      • Fabric Warehouse
      • Synapse Dedicated SQL Pool
      • Azure SQL Database, Azure SQL Managed Instance, Microsoft SQL Server
    • Tables
    • Views
    • Procedures
    • Functions
    • Triggers
    • Performance tests
  • Scripter
    • About
    • Supported databases
    • SQL Server
    • Azure SQL
    • Synapse Dedicated Pool
    • Oracle
    • PostgreSQL
    • MySQL
Powered by GitBook
On this page
  • Emulation context
  • Emulation strategy
  • Code example
  • Important notes
  1. Emulation Scenarios
  2. SQL Server to Fabric Warehouse
  3. Cursors

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.

PreviousCursors in control flowNextData modification cursors

Last updated 5 days ago

Nested cursors emulation in SQL Tran