Declaring SQL variables in Notebooks

[NotebookEnjoyer]

Hello, I have an, I guess, trivial question. In a notebook, this code throws a syntax error:

%%sql

declare @myvar int;

select @myvar = 254812;

I suppose I can't use variables with cell magic, but I can't find any official sources about the limits. Generally, the difference in SQL syntax and capabilities between the lakehouse SQL endpoint and notebook cells annoys me. Does anyone happen to know where there is a tidy overview about the differences?

Thanks in advance!

[v-prasare · Accepted solution]

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

[NandanHegde]

You can use normal variables in notebook.

And then you can use spark.sql() to execute your query.

[NotebookEnjoyer]

Thanks, but I know that. I'm not looking for a workaround, but rather for an explanation and an official source of information.