Multiple cursors

Emulation context

In SQL Server, procedures can declare and operate on multiple independent cursors within the same scope. Each cursor can manage its own result set, loop logic, and variable assignments, enabling scripts to work with multiple datasets side by side or sequentially.

Common patterns include:

• Sequential processing of different datasets (e.g., one cursor over people, another over emails).

• Parallel use of unrelated cursors within the same procedure.

• Switching between cursors in mixed logic flows.

However, Microsoft Fabric Warehouse does not natively support cursors, and SQL Tran must translate each declared cursor into an emulated structure using temporary tables. This introduces naming, scoping, and isolation challenges, especially when multiple cursors are present in the same procedure.

---

Emulation strategy

SQL Tran emulates multiple cursors by:

• Translating each declared cursor into its own dedicated temporary table (e.g., #person_cursor_1, #person_cursor_2).

• Creating independent row counters and control variables (e.g., @CurrentRow_person_cursor_1, @CurrentRow_person_cursor_2) for each cursor.

• Ensuring that the loops operate sequentially and independently so that the state of one cursor does not affect the other.

• Materializing the result sets separately before each cursor’s loop begins.

Key transformation behaviors:

• Naming consistency: SQL Tran systematically applies numbered or suffixed names to distinguish each temp table and control variable, even if the original cursor names differ only slightly.

• Loop isolation: Each cursor’s iteration logic is encapsulated, ensuring no cross-interference between active loops.

• Sequential cleanup: Temporary tables are dropped after each cursor completes, maintaining clear separation in resource use.

While this approach is structurally robust, it can introduce performance overhead, especially if the procedure repeatedly creates and drops multiple large temp tables. Manual review is recommended if optimization or set-based rewriting is feasible.

---

Code example

SQL Server:

CREATE PROCEDURE dbo.ProcessPeople
AS
BEGIN
	DECLARE @BusinessEntityID INT, @LastName NVARCHAR(50), @Email NVARCHAR(50);
	-- First cursor
	DECLARE person_cursor_1 CURSOR FOR
	SELECT BusinessEntityID, LastName FROM Person.Person;
	OPEN person_cursor_1;
	FETCH NEXT FROM person_cursor_1 INTO @BusinessEntityID, @LastName;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Placeholder for logic
		FETCH NEXT FROM person_cursor_1 INTO @BusinessEntityID, @LastName;
	END;
	CLOSE person_cursor_1;
	DEALLOCATE person_cursor_1;
	-- Second cursor
	DECLARE person_cursor_2 CURSOR FOR
	SELECT EmailAddress FROM Person.EmailAddress;
	OPEN person_cursor_2;
	FETCH NEXT FROM person_cursor_2 INTO @Email;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- Placeholder for logic
		FETCH NEXT FROM person_cursor_2 INTO @Email;
	END;
	CLOSE person_cursor_2;
	DEALLOCATE person_cursor_2;
END

Fabric Warehouse (generated by SQL Tran):

CREATE PROCEDURE dbo.ProcessPeople
AS
BEGIN
	DECLARE @BusinessEntityID INT, @LastName NVARCHAR(50), @Email NVARCHAR(50);
	-- First cursor
	DECLARE @RowCount_person_cursor_1 INT;
DECLARE @CurrentRow_person_cursor_1 INT = 1;
CREATE TABLE #person_cursor_1 (
	RowNum INT, BusinessEntityID INT, LastName NVARCHAR(50)
)INSERT
	INTO #person_cursor_1 (RowNum, BusinessEntityID, LastName)
	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
		BusinessEntityID,
		LastName
	FROM
		Person.Person;
SELECT @RowCount_person_cursor_1 = COUNT(*)
FROM
	#person_cursor_1;
	
	SELECT @BusinessEntityID = BusinessEntityID,
	@LastName = LastName
FROM
	#person_cursor_1
WHERE
	RowNum = @CurrentRow_person_cursor_1;
	WHILE @CurrentRow_person_cursor_1 <= @RowCount_person_cursor_1
	BEGIN
		-- Placeholder for logic
		SET @CurrentRow_person_cursor_1 = @CurrentRow_person_cursor_1 + 1;
SELECT @BusinessEntityID = BusinessEntityID,
	@LastName = LastName
FROM
	#person_cursor_1
WHERE
	RowNum = @CurrentRow_person_cursor_1;
	END;
	
	DROP TABLE IF EXISTS #person_cursor_1;
	-- Second cursor
	DECLARE @RowCount_person_cursor_2 INT;
DECLARE @CurrentRow_person_cursor_2 INT = 1;
CREATE TABLE #person_cursor_2 (
	RowNum INT, EmailAddress NVARCHAR(50)
)INSERT
	INTO #person_cursor_2 (RowNum, EmailAddress)
	SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
		EmailAddress
	FROM
		Person.EmailAddress;
SELECT @RowCount_person_cursor_2 = COUNT(*)
FROM
	#person_cursor_2;
	
	SELECT @Email = EmailAddress
FROM
	#person_cursor_2
WHERE
	RowNum = @CurrentRow_person_cursor_2;
	WHILE @CurrentRow_person_cursor_2 <= @RowCount_person_cursor_2
	BEGIN
		-- Placeholder for logic
		SET @CurrentRow_person_cursor_2 = @CurrentRow_person_cursor_2 + 1;
SELECT @Email = EmailAddress
FROM
	#person_cursor_2
WHERE
	RowNum = @CurrentRow_person_cursor_2;
	END;
	
	DROP TABLE IF EXISTS #person_cursor_2;
END

Multiple cursors emulation in SQL Tran

---

Important notes

• Independent temp tables: Each cursor is materialized into a separate temp table, ensuring isolated datasets.

• Isolated loop variables: SQL Tran generates independent control variables for each cursor. However, naming collisions can occur if cursors are not uniquely named in the original code.

• Sequential execution: Even though Fabric is a parallelizable engine, multiple cursor loops are translated into deterministic, serial loops, reflecting the original procedural intent.

• Performance impact: Creating and dropping multiple temp tables, especially on large datasets, can introduce significant overhead. Where possible, set-based rewrites should be considered for optimization.

• Manual review recommended: Users should review translated code carefully, especially when multiple cursors interact with shared resources or variables, to ensure no unintended cross-cursor dependencies exist.