在 T-SQL 中使用环境变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/856073/
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
Using Environment variables in T-SQL
提问by benPearce
How can I read the value of a system environment variable in a T-SQL script?
如何在 T-SQL 脚本中读取系统环境变量的值？
This is to run on SQL Server 2005.
这是在 SQL Server 2005 上运行。
采纳答案by Sam Saffron
This should give you a list (provided you allow people to execute xp_cmdshell)
这应该给你一个列表（前提是你允许人们执行 xp_cmdshell）
exec master..xp_cmdshell 'set'
exec master..xp_cmdshell '设置'
Note: xp_cmdshell is a security hazard ...
注意：xp_cmdshell 存在安全隐患...
You could also do this with a managed stored proc an extended stored proc or via a com component.
您还可以使用托管存储过程、扩展存储过程或通过 com 组件执行此操作。
回答by user197525
To "read the value of a system environment variable in a T-SQL script" you can set SQL Management Studio to use "sqlcmd Mode".
要“读取 T-SQL 脚本中系统环境变量的值”，您可以将 SQL Management Studio 设置为使用“sqlcmd 模式”。
Then you can use like this:
然后你可以像这样使用：
Print '$(TEMP)'
:r $(Temp)\Member.sql
go
I'm not sure how this is done outside of "SQL Management Studio" but it should be hard to find out.
我不确定这是如何在“SQL Management Studio”之外完成的，但应该很难找到。
回答by Aaron Alton
xp_cmdshell is generally best avoided for security reasons.
出于安全原因，通常最好避免使用 xp_cmdshell。
You're better off using a CLR assembly. Here's a good introduction to creating a CLR assembly.
最好使用 CLR 程序集。这是创建 CLR 程序集的很好的介绍。
You can use System.Environment.GetEnvironmentVariable() in C# - you'll find more info on how to do that here.
您可以在 C# 中使用 System.Environment.GetEnvironmentVariable() - 您将在此处找到有关如何执行此操作的更多信息。
回答by benPearce
Thanks for the answers. They helped me get to a working solution, although this is probably not the most advanced method:
感谢您的回答。他们帮助我找到了一个可行的解决方案，尽管这可能不是最先进的方法：
declare @val varchar(50)
create table #tbl (h varchar(50))
insert into #tbl exec master..xp_cmdshell 'echo %computername%'
set @val = (select top 1 h from #tbl)
drop table #tbl
Specifically I was trying to get the hostname, the echo %computername% could be replaced with the hostnamesystem command. But this now works for any environment variable.
具体来说，我试图获取主机名，可以用主机名系统命令替换 echo %computername% 。但这现在适用于任何环境变量。
回答by Rodrigo
Hey, if you want to get the server name, just call SELECT @@SERVERNAME
嘿，如果你想得到服务器名称，只需调用 SELECT @@SERVERNAME
回答by MovGP0
To determine a specific environment variable in T-SQL (MS SQL Server) you can do something like:
要确定 T-SQL (MS SQL Server) 中的特定环境变量，您可以执行以下操作：
Grant Security Permissions
授予安全权限
use [master]
execute sp_configure 'show advanced options', 1
reconfigure
go
execute sp_configure 'xp_cmdshell', 1
reconfigure
go
grant execute on xp_cmdshell to [DOMAIN\UserName]
grant control server to [DOMAIN\UserName]
go
Source: https://stackoverflow.com/a/13605864/601990
来源：https: //stackoverflow.com/a/13605864/601990
Use Environment Variables
使用环境变量
-- name of the variable
declare @variableName nvarchar(50) = N'ASPNETCORE_ENVIRONMENT'
-- declare variables to store the result
declare @environment nvarchar(50)
declare @table table (value nvarchar(50))
-- get the environment variables by executing a command on the command shell
declare @command nvarchar(60) = N'echo %' + @variableName + N'%';
insert into @table exec master..xp_cmdshell @command;
set @environment = (select top 1 value from @table);
-- do something with the result
if @environment = N'Development' OR @environment = N'Staging'
begin
select N'test code'
end
else
begin
select N'prod code'
end
Also remember to restart the SQL Server Servicewhen changing the Environment Variables.
还要记住在更改环境变量时重新启动 SQL Server 服务。
