Trying SQL Notebooks in Azure Data Studio but variables declared in one cell are not carrying to new cell
Asked Answered
U

1

10

I'm trying to use SQL Notebooks similar to how I'm used to using Jupyter Notebooks for documenting some standard queries I use. However when I declare a table variable in one cell, the value isn't accessible in a new cell. I do this so I can annotate each cell to explain why I am doing these operations. Is this a limitation of SQL Notebooks? Or is there a declaration I am missing?

Screenshot from Azure Data Studio of error

Underlying answered 31/3, 2020 at 0:47 Comment(3)
Each cell is an independent batch, just like with GO batch separators in a query window. I suggest you annotate T-SQL code with comments as needed and use text cells, optionally with markup, for instructions and documentation.Letta
This kind of defeats the purpose of a Jupyter Notebook though, where you're supposed to be able to "prettify" the stuff that normally goes in -- comments -- and also be able to run pieces of code out of order to allow experimentation and see the effect of subtle changes on a series of operations for a longer more complex transactionUnderlying
Exactly. Plus, it is not the way Jupyter Notebooks work in other languages.Engud
A
5

There's a workaround using SQL itself and Session Context to declare variables between your blocks.

See here: https://www.ericgharrison.com/?p=418

With a Session Context setting, we can store the values using sp_set_session_context…

EXEC sp_set_session_context 'StartDate', '11/01/2020' EXEC sp_set_session_context 'EndDate', '11/01/2020 23:59:59.99'

…and then retrieve and use them in another code block:

DECLARE @StartDate DATETIME DECLARE @EndDate DATETIME SELECT @StartDate = CAST(SESSION_CONTEXT(N'StartDate') AS DATETIME) SELECT @EndDate = CAST(SESSION_CONTEXT(N'EndDate') AS DATETIME)

Abscind answered 3/8, 2021 at 6:28 Comment(0)

© 2022 - 2025 — McMap. All rights reserved.