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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   lt html gt  lt head gt  lt style gt body     globalDatabase dbo global GetStandardFonts        lt  style gt  lt  head gt  lt body gt       --  Result   lt html gt  lt head gt  lt style gt body font-family  Calibri Light   sans-serif    lt  style gt  lt  head gt  lt body gt

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

[sql] how to declare global variable in SQL Server..?

Examples related to sql

Examples related to sql-server