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

Dale K
  • 25,246
  • 15
  • 42
  • 71
reddi.tech
  • 2,183
  • 1
  • 16
  • 18
  • 2
    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. – Dan Guzman Mar 31 '20 at 10:24
  • 4
    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 transaction – reddi.tech Mar 31 '20 at 22:37
  • Exactly. Plus, it is not the way Jupyter Notebooks work in other languages. – Phierru Feb 09 '23 at 09:58

1 Answers1

4

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)

Alisneaky
  • 205
  • 1
  • 3
  • 15