{"id":418,"date":"2020-11-24T08:10:13","date_gmt":"2020-11-24T13:10:13","guid":{"rendered":"https:\/\/www.ericgharrison.com\/?p=418"},"modified":"2020-11-24T08:10:14","modified_gmt":"2020-11-24T13:10:14","slug":"using-variables-across-blocks-in-azure-data-studio-for-sql","status":"publish","type":"post","link":"https:\/\/www.ericgharrison.com\/?p=418","title":{"rendered":"Using variables across blocks in Azure Data Studio for SQL"},"content":{"rendered":"\n<p class=\"wp-block-paragraph\">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&#8217;t be passed across blocks (each one is completely independent), I end up having to edit variables in each block, which slows things down.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">After a bit of searching around, I found a solution that seems to work pretty well.  It uses a feature I hadn&#8217;t heard of before, which are session context settings.  <\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-stored-procedures\/sp-set-session-context-transact-sql\">https:\/\/docs.microsoft.com\/en-us\/sql\/relational-databases\/system-stored-procedures\/sp-set-session-context-transact-sql<\/a><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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&#8217;re creating a notebook, presumably the value is there!<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">First we need to figure out what values we want to be able to re-use across blocks.  In this case, I&#8217;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.<\/p>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A sample query I might be wanting to spread across blocks:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE @StartDate AS DATETIME\nDECLARE @EndDate AS DATETIME\n\nSET @StartDate = '11\/01\/2020'\nSET @EndDate = '11\/01\/2020 23:59:59.99'\n\n\/* Get the basic info we need... *\/\nSELECT\n    *\nFROM\n    table\nWHERE\n    CreatedOn BETWEEN @StartDate AND @EndDate\n\n\/* Instructions go here on how to troubleshoot related stuff *\/\nSELECT\n    *\nFROM\n    AnotherTable\nWHERE\n    CreatedOn > @StartDAte<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator\"\/>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>With a Session Context setting, we can store the values using sp_set_session_context&#8230;<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>EXEC sp_set_session_context 'StartDate', '11\/01\/2020'\nEXEC sp_set_session_context 'EndDate', '11\/01\/2020 23:59:59.99'<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>&#8230;and then retrieve and use them in another code block:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>DECLARE @StartDate DATETIME\nDECLARE @EndDate DATETIME\nSELECT @StartDate = CAST(SESSION_CONTEXT(N'StartDate') AS DATETIME)\nSELECT @EndDate = CAST(SESSION_CONTEXT(N'EndDate') AS DATETIME)\n\nSELECT\n    *\nFROM\n    YourTable\nWHERE\n    CreatedOn BETWEEN @StartDate AND @EndDate<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Since these settings are session based, when you close out the notebook, or change the connection, they are discarded.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">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 &#8211; it&#8217;s one of the shorter ones from Microsoft, so it&#8217;s pretty digestible.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8217;t be [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18],"tags":[19,20,21],"class_list":["post-418","post","type-post","status-publish","format-standard","hentry","category-sql","tag-ads","tag-azure-data-studio","tag-sql"],"_links":{"self":[{"href":"https:\/\/www.ericgharrison.com\/index.php?rest_route=\/wp\/v2\/posts\/418","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ericgharrison.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ericgharrison.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ericgharrison.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ericgharrison.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=418"}],"version-history":[{"count":0,"href":"https:\/\/www.ericgharrison.com\/index.php?rest_route=\/wp\/v2\/posts\/418\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.ericgharrison.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=418"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ericgharrison.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=418"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ericgharrison.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=418"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}