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?
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)
