Azure Data Studio - Setting SQL variables to be used as globals

Question

In Azure Data Studio (ADS), is it possible to reuse SQL parameters from query to query? Not sure if I'm jumping out of the intended purpose of ADS but it would be really great if I could declare a set of variables in one code text (or anywhere) and have all my queries understand and utilize them. Something similar to Jupyter notebooks with Python, how you could do the global variables in one code block and all others would respect those variables.

In general, I am having little luck finding documentation on ADS other than the official Microsoft docs.

score 6 · Answer 1 · answered Nov 25 '19 at 15:28

6

As far as I know, no, because variables are in the scope of the current batch.

A workaround would be to create a temp table and insert that value. It's really gross, but it works.

In code cell #1:

CREATE TABLE #variableStorage (varname VARCHAR(100), val VARCHAR(100))
INSERT INTO #variableStorage
VALUES 
('SomeVariable', 'Foo')

CREATE TABLE #testing (ID INT, testval VARCHAR(100))

INSERT INTO #testing
VALUES 
(100, 'Foo')

In code cell #2:

SELECT *
from #testing

WHERE testval = (
    SELECT val 
    FROM #variableStorage 
    WHERE varname = 'SomeVariable'
    )

answered Nov 25 '19 at 15:28

dfundako

8,022
3
18
34

9

This is mind-boggling to me. I recognize why this limitation exists in context of _T-SQL_, but I'm shocked that the Azure Data Studio doesn't address this for _Notebooks_. As is, it really undermines the promise and capabilities of Notebooks as a way of composing and annotating related code. (I don't mean to shoot the messenger, and I appreciate the workaround—I'm just really surprised by this.) – Jeremy Caney Nov 16 '20 at 01:00
1

@JeremyCaney Read the most recent release notes for Azure Data Studio and look up Paramaters. That might be what you're looking for? – dfundako Nov 16 '20 at 14:39
That may well be. I was playing around with those last night, but hadn't yet made it to the documentation, and wasn't able to intuit my way through them on my own. I appreciate the pointer. – Jeremy Caney Nov 16 '20 at 19:32
I agree with you, @JeremyCaney. I was hoping that I could use the `DECLARE` statement to capture a value for a variable, but it doesn't work. I've been looking at Parameters, but from what I've found they look more like they're for passing values to a Jupyter notebook from the command line. – Rod Sep 15 '21 at 19:33

score 2 · Answer 2 · answered Nov 29 '19 at 07:48

There is no way to declare a global variable in Transact-SQL. So We also can not set global variables with ADS.

In earlier version:

The names of some Transact-SQL system functions begin with two at signs (@@). Although in earlier versions of SQL Server, the @@functions are referred to as global variables, they are not variables and do not have the same behaviors as variables. The @@functions are system functions, and their syntax usage follows the rules for functions.

Reference: Variables (Transact-SQL).

But there is another way can help you set the variable as the global variables with SSMS SQLCMD Mode.