如何在 SQL Server 中声明全局变量..？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22372359/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to declare global variable in SQL Server..?
提问by Umesh Kadam
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".
必须声明标量变量“@GLOBAL_VAR_2”。
Can any one suggest any way to do it...?
任何人都可以建议任何方法来做到这一点......？
采纳答案by Lanorkin
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 SQLCMDtool or the SQLCMD modeof SSMS and define that tool/mode-specific variables like this:
无法在 Transact-SQL 中声明全局变量。但是，如果您希望您的变量可以跨单个脚本的批处理访问，您可以使用SQLCMD工具或SSMS的SQLCMD 模式并定义该工具/模式特定的变量，如下所示：
:setvar myvar 10
and then use them like this:
然后像这样使用它们：
$(myvar)
To use SSMS's SQLCMD mode:
要使用 SSMS 的 SQLCMD 模式：
回答by Fedor Hajdu
You cannotdeclare global variables in SQLServer.
你不能在SQLServer的声明全局变量。
If you're using Management Studio you can use SQLCMD mode like @Lanorkin pointed out.
如果您使用的是 Management Studio，则可以使用 @Lanorkin 指出的 SQLCMD 模式。
Otherwise you can use CONTEXT_INFOto store a single variable that is visible during a session and connection, but it'll disappear after that.
否则，您可以使用CONTEXT_INFO存储在会话和连接期间可见的单个变量，但此后它将消失。
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.
只有真正全局的是创建一个全局临时表（名为##yourTableName），并将变量存储在那里，但是当所有连接都关闭时，它也会消失。
回答by jabeci
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)
回答by gotqn
Starting from SQL Server 2016a new way for sharing information in session is introduced via the SESSION_CONTEXTand sp_set_session_context.
从开始SQL Server 2016的新途径共享会话信息通过引入SESSION_CONTEXT和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.
您可以使用它们作为替代，CONTEXT_INFO()只保留限制为 128 字节的二进制值。此外，用户可以随时重写该值，将其用于安全检查不是很好。
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:
此外，我们可以将其标记为read-only：
EXEC sp_set_session_context 'user_id', 4, @read_only = 1;
If you try to modify a read-onlysession context you will get something like this:
如果您尝试修改read-only会话上下文，您将得到如下内容：
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.
消息 15664，级别 16，状态 1，过程 sp_set_session_context，第 10 行无法在会话上下文中设置键“user_id”。此会话的密钥已设置为只读。
回答by Russell Fox
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>...
回答by Saini
Try to use ;instead of GO. It worked for me for 2008 R2 version
尝试使用;而不是GO. 它适用于 2008 R2 版本
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;
回答by Christopher J. Grace
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?
我喜欢为每个全局变量使用带有一列的表的方法。通过这种方式，您可以自动完成以帮助对变量的检索进行编码。该表可以限制为单行，如下所述：SQL Server：如何约束表以包含单行？
回答by Hini
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.
在那个特定的例子中，错误是因为 use 语句之后的 GO。GO 语句会重置环境，因此不存在用户变量。他们必须再次声明。全局变量问题的答案是否定的，至少 Sql server 版本等于或早于 2008 不存在全局变量。我不能保证较新的 sql server 版本也是如此。
Regards, Hini
问候， 希尼
回答by TomTom
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.
无法在 SQL Server 中声明全局变量。Sql server 有全局变量的概念，但是它们是系统定义的，不能扩展。
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 执行各种技巧-例如，SqlCOMmand 具有这样的变量替换机制-在您将其发送到 SqlServer 之前，仅此而已。
回答by user3414267
My first question is which version of SQL Server are you using (i.e 2005, 2008, 2008 R2, 2012)?
我的第一个问题是您使用的是哪个版本的 SQL Server（即 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 或更高版本的 SQL 使用范围进行变量确定。我相信 2005 年仍然有全局变量会使用 @@variablename 而不是 @variable name，后者将定义全局变量和局部变量之间的区别。从 2008 年开始，我相信这已更改为范围定义的变量指定结构。例如，要创建一个全局变量，@variable 必须在过程、函数、视图等的开始处定义。在 2008 年及以后的系统函数中，@@defined 系统变量我相信。如果您解释了版本以及定义变量的位置以及您遇到的错误，我可以进一步解释。
