CODEWITHSUNDEEP
sqlt-sqlazure-sql-databaseazure-data-studio
Volearix
Volearix

Reputation: 1593

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

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.

enter image description here

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

Upvotes: 15

Views: 7730

Answers (3)

MikaData
MikaData

Reputation: 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.

Upvotes: 0

Leon Yue
Leon Yue

Reputation: 16431

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. enter image description here

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

Hope this helps.

Upvotes: 2

dfundako
dfundako

Reputation: 8324

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

Upvotes: 7

Related Questions