Windows environment variables are a great way to store server specific information that may be accessed from different systems on the server. Connection strings, folder paths and ip addresses are common examples of what may be suitable to store as environment variables on the server.
To add or edit a Windows environment variable, right click on “Computer” -> “Properties” -> “Advanced System Settings” -> “Environment Variables”:
The upper part of the window that opens is used to manage “User variables”. Those variables are unique per user, so use those if you want the environment variable to vary depending on current user. The lower part contains the “System Variables”. Those variables are set per server.
A user or system variable consists of a variable name (the key) and a value.
To use an environment variable in T-SQL, use the following script and replace the [YOUR_ENVIRONMENT_VARIABLE] with the name of the environment variable:
DECLARE @ENV_VAR VARCHAR(MAX) CREATE TABLE #TMP_ENV_VAR (VALUE VARCHAR(MAX)) INSERT INTO #TMP_ENV_VAR EXEC xp_cmdshell 'echo %<YOUR_ENVIRONMENT_VARIABLE>%' SELECT TOP 1 @ENV_VAR = VALUE FROM #TMP_ENV_VAR WHERE VALUE IS NOT NULL AND SUBSTRING(VALUE,1,1) <> '%' ORDER BY VALUE DROP TABLE #TMP_ENV_VAR SELECT @ENV_VAR
A couple of notes on the script.
