how to declare global variable in SQL Server..?

Question

I want to use same value for different queries from different DB

like

DECLARE @GLOBAL_VAR_1 INT = Value_1

DECLARE @GLOBAL_VAR_2 INT = Value_2

USE "DB_1"
GO
SELECT * FROM "TABLE" WHERE "COL_!" = @GLOBAL_VAR_1 

AND "COL_2" = @GLOBAL_VAR_2

USE "DB_2"
GO

SELECT * FROM "TABLE" WHERE "COL_!" = @GLOBAL_VAR_2

but its giving error.

Must declare the scalar variable "@GLOBAL_VAR_2".

Can any one suggest any way to do it...?

Check if this link helps. http://weblogs.sqlteam.com/mladenp/archive/2007/04/23/60185.aspx — samar, Mar 13 '14 at 08:16
How "Global" are you looking for? Usable across batches is obvious from your sample script, but should the values be visible/modifiable for other connections? — Damien_The_Unbeliever, Mar 13 '14 at 09:08

score 40 · Accepted Answer · edited Mar 20 '14 at 06:36

40

There is no way to declare a global variable in Transact-SQL. However, if all you want your variables for is to be accessible across batches of a single script, you can use the SQLCMD tool or the SQLCMD mode of SSMS and define that tool/mode-specific variables like this:

:setvar myvar 10

and then use them like this:

$(myvar)

To use SSMS's SQLCMD mode:

enter image description here

edited Mar 20 '14 at 06:36

Andriy M

76,112
17
94
154

answered Mar 13 '14 at 08:07

Lanorkin

7,310
2
42
60

@TomTom It is relevant in that it allows you to declare a variable that spans batches. – Martin Smith Mar 13 '14 at 08:12
1

@TomTom I do care about question, and looks like you don't. For sqlcmd mode it doesn't matter if it is used in sqlcmd command tool or visual UI. I provided a solution, while minus vote means "answer is not useful". And yes, I insist you are rude. – Lanorkin Mar 13 '14 at 08:18
7

@TomTom I'll try last time. Idea of StackOverflow not to answer "No, its' not possible", but to show a way how it can be done. It doesn't matter what 'exactly' asked - people don't know answers to questions they ask, so they need ideas around. It's strange that you behave so having 30k. – Lanorkin Mar 13 '14 at 08:24
@Lanorkin, what are the implications of using `SQLCMD` mode? – craig Jul 27 '15 at 21:31
@craig, it's a separate big question, but in general - `SQLCMD` is `SQL` and `CMD`, so having enough permissions you can do anything inside OS - which might be desired behavior or something you would like to avoid – Lanorkin Jul 28 '15 at 08:09
This is workable but the better answer for the given example is probably to remove the GO statements so the statements run in a single batch. A lot of people seem to think GO is required after a USE statement, but it is not. – Nathan Griffiths Sep 19 '17 at 00:37
@Nathan GO is needed for Setting IDENTITY_INSERT – Sarfaraaz Mar 08 '18 at 14:47
1

@Sarfaraaz GO is *not* required for setting IDENTITY_INSERT. GO is a batch terminator and there's nothing in the question code that indicates any need to split the queries up into batches using GO. See the answer from Saini below for the correct answer to this question. – Nathan Griffiths Mar 10 '18 at 01:35
Too bad @TomTom deleted their comments, I'm curious how they commented on this answer – Denny Jul 18 '19 at 09:36

Fedor Hajdu · Answer 2 · 2014-03-13T08:34:40.123

20

You cannot declare global variables in SQLServer.

If you're using Management Studio you can use SQLCMD mode like @Lanorkin pointed out.

Otherwise you can use CONTEXT_INFO to store a single variable that is visible during a session and connection, but it'll disappear after that.

Only truly global would be to create a global temp table (named ##yourTableName), and store your variables there, but that will also disappear when all connection are closed.

edited Mar 13 '14 at 08:34

answered Mar 13 '14 at 08:25

Fedor Hajdu

4,657
3
32
50

2

Actually I think regarding the multiple queries a normal temp table may also work (not a global one). THe idea with a temp table is neat... as good as it gets within the parameters defined. – TomTom Mar 13 '14 at 08:32
2

if ##yourTableName is created when the database is created in the db script then it will persist (and have minimal logging) – whytheq Mar 13 '14 at 09:29

score 13 · Answer 3 · edited Dec 11 '14 at 20:45

13

You could try a global table:

create table ##global_var
            (var1 int
            ,var2 int)

USE "DB_1"
GO
SELECT * FROM "TABLE" WHERE "COL_!" = (select var1 from ##global_var) 

AND "COL_2" = @GLOBAL_VAR_2

USE "DB_2"
GO

SELECT * FROM "TABLE" WHERE "COL_!" = (select var2 from ##global_var)

edited Dec 11 '14 at 20:45

Kohlbrr

3,861
1
21
24

answered Dec 11 '14 at 20:26

jabeci

141
1
2

Is there downsides to using a global table to keep track of variables? – Bas Oct 18 '18 at 12:25

score 12 · Answer 4 · answered Oct 06 '17 at 07:40

Starting from SQL Server 2016 a new way for sharing information in session is introduced via the SESSION_CONTEXT and sp_set_session_context.

You can use them as alternative of CONTEXT_INFO() which persist only a binary value limited to 128 bytes. Also, the user can rewrite the value anytime and it's not very good to use it for security checks.

The following issues are resolved using the new utils. You can store the data in more user-friendly format:

EXEC sp_set_session_context 'language', 'English';  
SELECT SESSION_CONTEXT(N'language');

Also, we can mark it as read-only:

EXEC sp_set_session_context 'user_id', 4, @read_only = 1;

If you try to modify a read-only session context you will get something like this:

Msg 15664, Level 16, State 1, Procedure sp_set_session_context, Line 10 Cannot set key 'user_id' in the session context. The key has been set as read_only for this session.

score 8 · Answer 5 · answered Sep 09 '15 at 21:19

You can get a similar result by creating scalar-valued functions that return the variable values. Of course, function calls can be expensive if you use them in queries that return a large number of results, but if you're limiting the result-set you should be fine. Here I'm using a database created just to hold these semi-static values, but you can create them on a per-database basis, too. As you can see, there are no input variables, just a well-named function that returns a static value: if you change that value in the function, it will instantly change anywhere it's used (the next time it's called).

USE [globalDatabase]
GO

CREATE FUNCTION dbo.global_GetStandardFonts ()
RETURNS NVARCHAR(255)
AS
BEGIN
    RETURN 'font-family:"Calibri Light","sans-serif";'
END
GO

--  Usage: 
SELECT '<html><head><style>body{' + globalDatabase.dbo.global_GetStandardFonts() + '}</style></head><body>...'

--  Result: <html><head><style>body{font-family:"Calibri Light","sans-serif";}</style></head><body>...

You can make this an inline function to avoid the performance hit. — Christopher J. Grace, Aug 03 '18 at 00:26
@ChristopherJ.Grace: thx for pointing that out! Found more info on it here: https://www.infoq.com/news/2019/01/SQL-Server-Scalar-UDF-Inlining — Wouter, May 07 '19 at 09:05

score 7 · Answer 6 · edited Nov 25 '17 at 11:40

7

Try to use ; instead of GO. It worked for me for 2008 R2 version

DECLARE @GLOBAL_VAR_1 INT = Value_1;

DECLARE @GLOBAL_VAR_2 INT = Value_2;

USE "DB_1";
SELECT * FROM "TABLE" WHERE "COL_!" = @GLOBAL_VAR_1 

AND "COL_2" = @GLOBAL_VAR_2;

USE "DB_2";

SELECT * FROM "TABLE" WHERE "COL_!" = @GLOBAL_VAR_2;

edited Nov 25 '17 at 11:40

shA.t

16,580
5
54
111

answered Jul 15 '16 at 00:03

Saini

113
1
2
5

2

For the given example, this is the best solution. The GO statement terminates the current batch (and any local variables that had been declared), so as long as you are OK running all the statements in the same batch it's not needed. – Nathan Griffiths Sep 19 '17 at 00:42

score 2 · Answer 7 · answered Sep 15 '14 at 17:42

In that particular example, the error it's because of the GO after the use statements. The GO statements resets the environment, so no user variables exists. They must be declared again. And the answer to the global variables question is No, does not exists global variables at least Sql server versions equal or prior to 2008. I cannot assure the same for newer sql server versions.

Regards, Hini

score 2 · Answer 8 · answered Aug 03 '18 at 00:36

I like the approach of using a table with a column for each global variable. This way you get autocomplete to aid in coding the retrieval of the variable. The table can be restricted to a single row as outlined here: SQL Server: how to constrain a table to contain a single row?

score 0 · Answer 9 · answered Mar 13 '14 at 08:15

It is not possible to declare global variables in SQL Server. Sql server has a concept of global variables, but they are system defined and can not be extended.

obviously you can do all kinds of tricks with the SQL you are sending - SqlCOmmand has such a variable replacement mechanism for example - BEFORE you send it to SqlServer, but that is about it.

score 0 · Answer 10 · answered May 08 '23 at 23:00

Inspired by @Russell Fox answer, you can create a function to search in a table of global variables:

CREATE SCHEMA G -- G for Global
GO
CREATE TABLE G.VAR (
    NAME VARCHAR(100) PRIMARY KEY CLUSTERED
    ,VALUE VARCHAR(100) )
GO
CREATE PROC G.PSET (@NAME VARCHAR(100), @VALUE VARCHAR(100)) AS BEGIN
         --Insert or update a variable
    MERGE G.VAR T
    USING (SELECT N=@NAME, V=@VALUE) N
        ON NAME = N
        WHEN MATCHED THEN
            UPDATE
            SET VALUE = V
        WHEN NOT MATCHED THEN
            INSERT VALUES (N, V);
END
GO
CREATE FUNCTION G.FGET(@NAME VARCHAR(100)) RETURNS VARCHAR(100) AS BEGIN
    RETURN (
        SELECT TOP 1 VALUE
        FROM G.VAR
        WHERE NAME = @NAME)
END

Usage:

    --Set new variable or update it
EXEC G.PSET 'name', 'erick de vathaire'
    --Get current value
PRINT 'hi, i am ' + g.fget('name')

score -3 · Answer 11 · answered Mar 13 '14 at 08:04

My first question is which version of SQL Server are you using (i.e 2005, 2008, 2008 R2, 2012)?

Assuming you are using 2008 or later SQL uses scope for variable determination. I believe 2005 still had global variables that would use @@variablename instead of @variable name which would define the difference between global and local variables. Starting in 2008 I believe this was changed to a scope defined variable designation structure. For example to create a global variable the @variable has to be defined at the start of a procedure, function, view, etc. In 2008 and later @@defined system variables for system functions I do believe. I could explain further if you explained the version and also where the variable is being defined, and the error that you are getting.

2008 also has global variables. Problem is - you can not define them ;( — TomTom, Mar 13 '14 at 08:31

score -4 · Answer 12 · edited Oct 17 '16 at 22:41

-4

declare @ID_var int
set @ID_var = 123456

select * from table where ID_var = @ID_var

or

declare @ID_var varchar(30)
set @ID_var = 123456

select * from table where ID_var = @ID_var

edited Oct 17 '16 at 22:41

Baum mit Augen

49,044
25
144
182

answered Oct 17 '16 at 22:40

user7033522

3

2

The question is specifically for variables that can be used in multiple separate queries. – Igor Feb 12 '19 at 16:46

how to declare global variable in SQL Server..?

12 Answers12

Linked