Declaring SQL variables in Notebooks

Hi NotebookEnjoyer,

%sql in notebooks cannot handle T‑SQL pragmas like DECLARE or SET are part of T-SQL (Transact-SQL), a richer SQL dialect used by platforms like SQL Server or Lakehouse SQL endpoints. These platforms are designed to handle advanced operations such as control-of-flow, variable declarations, and stored procedures.

Notebook %sql magics are intended for lightweight, interactive querying during data exploration, not for executing full T-SQL scripts. Their goal is simplicity and speed, often at the expense of functionality.

Use a Lakehouse / Synapse SQL endpoint: These endpoints support full T‑SQL syntax, including variable declarations, control flow (BEGIN...END), and stored procedures. You can run these scripts in dedicated SQL script editors or SQL cells—not inside %sql magic cell.

If procedural features are needed in a notebook, you can use Python or PySpark to define variables and control logic, and then dynamically build or pass SQL queries to spark.sql() This approach is flexible and integrates well with notebook workflows.

Thanks,

Prashanth Are

MS Fabric community support