Cursors

Emulation context

In SQL Server, cursors provide a mechanism for iterating over query results row by row, often allowing complex, procedural logic that cannot easily be expressed in set-based operations. Cursors are frequently used in administrative scripts, reporting processes, and data transformation routines that require per-row control, conditional logic, or nested processing.

However, Microsoft Fabric Warehouse is architecturally different from SQL Server. It is a distributed, analytics-optimized platform that prioritizes set-based processing over row-by-row operations. The concept of a cursor — which depends on a session-local pointer moving through a result set — does not naturally fit the execution model of Fabric. This presents a fundamental compatibility challenge for direct migrations.

To bridge this gap, SQL Tran emulates cursor behavior by converting imperative cursor loops into deterministic, row-by-row logic using temporary tables and iteration constructs. This allows translated code to preserve the semantics of cursor-driven logic while remaining compatible with the distributed, stateless architecture of Fabric.

SQL Tran does not simply flag cursors as unsupported — it reconstructs their behavior by:

• Materializing the result set into a temporary table with explicit row numbers.

• Rewriting the loop to iterate over those rows using scalar variables and control-flow constructs.

• Preserving the structure and order of cursor-based logic as closely as possible.

This approach makes it possible to translate a wide variety of cursor patterns — including nested cursors, conditional declarations, and multi-table joins — with minimal manual intervention.

While the emulation is comprehensive, it also introduces important behavioral differences due to the lack of live data sensitivity and other SQL Server-specific semantics. These are addressed in the emulation strategy and important notes sections below.

---

Emulation strategy

SQL Server supports a wide range of cursor patterns — from simple forward-only iterations to nested loops, conditional declarations, and data-modifying operations. Reconstructing these patterns in Fabric Warehouse requires more than a one-size-fits-all approach.

SQL Tran emulates each cursor scenario by transforming the original logic into a deterministic row-by-row loop based on temporary tables. These tables materialize the cursor’s result set, assign explicit row numbers, and drive the iteration process numerically. This approach ensures consistent behavior while remaining compatible with Fabric’s distributed architecture and lack of cursor support.

Because cursor usage varies significantly across workloads, each emulation pattern is documented on its own page. These focused pages include source and translated code examples, specific translation strategies, and known limitations where applicable.

The following cursor patterns are covered:

• Basic cursor loop
• Cursor types
• Fetch direction modes
• Cursors in control flow
• Nested cursors
• Data modification cursors
• Multiple cursors
• Subqueries and filtering

The following general limitations apply across all cursor emulation scenarios and should be reviewed before applying translated code.

---

Important notes

SQL Tran enables reliable translation of most cursor-based logic to Microsoft Fabric, but due to architectural and behavioral differences, certain limitations apply.

• Static result sets: SQL Tran rewrites all cursor queries into pre-populated temporary tables. As a result, the dataset is fixed at the start of the loop — changes to underlying tables during iteration are not visible. This differs from DYNAMIC cursors in SQL Server.

• Limited FETCH mode support: Only FETCH NEXT and FETCH PRIOR are supported inside loops. Other modes like FETCH RELATIVE and FETCH ABSOLUTE inside loops cause translation errors. Outside the loop, these modes are silently ignored. See the Fetch Direction Modes page for details.
• WHERE CURRENT OF limitations: The translated code preserves the WHERE CURRENT OF syntax but does not bind it correctly to the emulated loop. Updates appear structurally valid but do not affect the intended row. Manual replacement with explicit key-based WHERE clauses is required.

• FOR UPDATE ignored: The FOR UPDATE clause is included in translated queries but has no functional effect in Fabric. If concurrency control is needed, it must be reimplemented.

• Strict FETCH INTO matching: SQL Tran requires an exact match between the number of selected columns and the number of destination variables in FETCH INTO. Partial assignment or mismatched counts trigger translation errors.

• Conditional and looped DECLARE CURSOR: Declaring cursors inside IF / ELSE blocks or loops is supported, but repeated declarations can cause duplicated temporary table names and control variable reuse. Review translations carefully to avoid naming conflicts.

• Temporary table reuse issues: Using the same cursor name in multiple blocks or nested loops may produce overlapping temp table definitions. While SQL Tran attempts to isolate naming, manual refactoring may be needed.

• ORDER BY with SELECT TOP rewriting issues: If a cursor uses both TOP and ORDER BY, the translation may misplace the order logic. Manually verify the resulting query to ensure correct row ordering.

• Loss of specialized cursor semantics: Cursor-type behaviors specific to SQL Server (e.g., KEYSET, DYNAMIC) are not preserved. All translated loops operate over static, materialized snapshots.