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...?

Answer 1

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:

Answer 2

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.

Answer 3

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) 

Answer 4

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.