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...?
This question is related to
sql
sql-server
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.
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>...
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?
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;
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)
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.
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.
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
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.
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
Source: Stackoverflow.com