Skip to content
Skip to content
Eric G. Harrison's Blog

Not as nerdy as I used to be, but still pretty geeky

  • About
← This is why I have a “Kindle Over $10” list for Kindle books
What seven ICU nurses want you to know about the battles against Covid-19 →
-->

Using variables across blocks in Azure Data Studio for SQL

Posted on November 24, 2020 by ericgharrison

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.

This entry was posted in SQL and tagged ADS, Azure Data Studio, SQL. Bookmark the <a href="https://www.ericgharrison.com/?p=418" title="Permalink to Using variables across blocks in Azure Data Studio for SQL" rel="bookmark">permalink</a>.
← This is why I have a “Kindle Over $10” list for Kindle books
What seven ICU nurses want you to know about the battles against Covid-19 →

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2025 | Blog info WordPress Theme | By Bharat Kambariya