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.

Fugleman

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

Ragman

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.) – Camembert

@JeremyCaney Read the most recent release notes for Azure Data Studio and look up Paramaters. That might be what you're looking for? – Ragman

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. – Camembert

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. – Wicklund

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.

Surefire

Answer 3

Yes, you can define also sql statement as a variable and build it first, then execute it:

DECLARE @TableName NVARCHAR(50) = 'Employees';
DECLARE @ColumnName NVARCHAR(50) = 'FirstName';

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = N'SELECT ' + QUOTENAME(@ColumnName) + ' FROM ' + QUOTENAME(@TableName);

EXEC sp_executesql @SQL;

I just needed the same feature and have tested it with Azure Data Studio.

Gatling