Forum Discussion

NotebookEnjoyer's avatar
NotebookEnjoyer
Advocate II
1 year ago
Solved

Declaring SQL variables in Notebooks

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!
data engineering
notebook
  • v-prasare's avatar
    v-prasare
    1 year ago

    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

3 Replies

  • v-prasare's avatar
    v-prasare
    Community Support
    1 year ago

    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's avatar
    NandanHegde
    Super User
    1 year ago

    You can use normal variables in notebook.

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

    • NotebookEnjoyer's avatar
      NotebookEnjoyer
      Advocate II
      1 year ago

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