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.

Answer 1

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

Answer 2

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.

You can reference this blog:how to declare global variable in SQL Server..?

Hope this helps.