Subqueries and filtering

Emulation context

In SQL Server, cursors can be declared over complex queries, including those containing subqueries, WHERE filters, JOIN clauses, and computed conditions. These constructs allow the cursor to work on a precisely defined subset of rows, often reflecting relational or hierarchical logic.

Examples include:

• Cursors that only iterate over parent rows matching child table conditions (via IN, EXISTS, or correlated subqueries).

• Cursors that incorporate filtering or aggregation logic directly in their declaration query.

• Cursors over joined datasets combining multiple tables.

Microsoft Fabric Warehouse processes queries differently. It materializes result sets in advance and uses static temporary tables for iteration. This means the filtering and subquery logic must be fully resolved before the row-by-row loop begins. Any mismatch or unsupported construct in the subquery can affect the correctness or performance of the translated cursor logic.

---

Emulation strategy

SQL Tran emulates cursors with subqueries and filtering by:

• Translating the entire cursor declaration query (including subqueries, IN conditions, and JOIN clauses) into a pre-materialized temporary table (e.g., #person_cursor).

• Ensuring that all filtering happens at the time the temporary table is populated, so the iterative loop only processes the already-filtered set.

• Preserving the structural logic of the subquery or filtering condition in the translated SQL, provided it is compatible with Fabric’s query engine.

Key transformation behaviors:

• Subqueries flattened: Any subquery in the cursor declaration is preserved as part of the INSERT INTO that populates the temporary table.

• Filter logic preserved: WHERE conditions, IN clauses, and EXISTS constructs are retained in the translated query, ensuring that the temporary table only contains qualifying rows.

• Pre-execution resolution: The translated loop iterates over a static snapshot. Changes to the underlying tables during the loop do not affect the materialized result set.

While structurally sound, this approach assumes that subqueries are supported by Fabric and do not rely on unsupported constructs or procedural elements.

---

Code example

SQL Server:

CREATE PROCEDURE dbo.ProcessPeople
AS
BEGIN
	DECLARE @BusinessEntityID INT, @LastName NVARCHAR(50);
	DECLARE person_cursor CURSOR FOR
	SELECT BusinessEntityID, LastName FROM Person.Person
	WHERE BusinessEntityID IN (
		SELECT BusinessEntityID FROM Person.EmailAddress
	);
	OPEN person_cursor;
	FETCH NEXT 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
	WHERE
		BusinessEntityID IN (SELECT BusinessEntityID
		FROM
			Person.EmailAddress);
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 <= @RowCount_person_cursor
	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

Subqueries and filtering cursor emulation in SQL Tran

---

Important notes

• Subquery preservation: SQL Tran retains the full subquery logic when translating the cursor’s source query. However, this relies on Fabric supporting the same SQL constructs without modification.

• Filter scope: All filtering — including WHERE clauses and subquery results — is resolved when populating the temporary table. The loop operates only on this static result set.

• Changes not reflected during loop: Any INSERT, UPDATE, or DELETE operations on the underlying tables during loop execution are not visible in the materialized temporary table. This may differ from dynamic cursor behavior in SQL Server.

• Manual review recommended: Subqueries using unsupported functions, procedural expressions, or session-specific constructs may fail or produce incomplete translations. Users should validate complex filters and adjust the logic for Fabric’s distributed SQL limitations.