Using variables across blocks in Azure Data Studio for SQL

I do a lot of troubleshooting using SQL data and while I find the Jupyter notebooks in Azure Data Studio (ADS) useful, one thing that is frustrating is the inability to pass variables across code blocks. I will frequently need to limit queries to a date range, or a customer, and since variables can’t be passed across blocks (each one is completely independent), I end up having to edit variables in each block, which slows things down.

After a bit of searching around, I found a solution that seems to work pretty well. It uses a feature I hadn’t heard of before, which are session context settings.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-set-session-context-transact-sql

The idea is that we will store the variable value that we want to access inside the session context in the first code block, and then retrieve it in following code blocks wherever we need it. It is a couple more lines of code, but if we’re creating a notebook, presumably the value is there!

First we need to figure out what values we want to be able to re-use across blocks. In this case, I’m going to use StartDate and EndDate. These will be start and end date / times that I can use to bracket the data in the query with a BETWEEN in the WHERE clause.

A sample query I might be wanting to spread across blocks:

DECLARE @StartDate AS DATETIME
DECLARE @EndDate AS DATETIME

SET @StartDate = '11/01/2020'
SET @EndDate = '11/01/2020 23:59:59.99'

/* Get the basic info we need... */
SELECT
    *
FROM
    table
WHERE
    CreatedOn BETWEEN @StartDate AND @EndDate

/* Instructions go here on how to troubleshoot related stuff */
SELECT
    *
FROM
    AnotherTable
WHERE
    CreatedOn > @StartDAte

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)

SELECT
    *
FROM
    YourTable
WHERE
    CreatedOn BETWEEN @StartDate AND @EndDate

Since these settings are session based, when you close out the notebook, or change the connection, they are discarded.

There are some important caveats regarding the amount of data that can be stored in the context session so be sure to read the above linked documentation – it’s one of the shorter ones from Microsoft, so it’s pretty digestible.