There are times when passing parameters does not work. For example: when dealing with DTS packages or in cases when an external (non-SQL Server) platform activates a process inside a SQL Server environment.
A useful option for solving the parameter passing problem is to set an environment variable in the windows operating system and then to get its value inside the SQL Server environment.
Here I describe a way to do it. To my surprise, I didn't find any system procedure that would do it, so I coded my own SP called sp_GetEnvVarValue. The procedure gets the environment variable key to search and returns the value. If a key is not found the NULL is returned. I compiled it in the master database for use by all user databases.
Here's the code:
Create Procedure dbo.sp_GetEnvVarValue (@EnvVarName sysname, @ResultValue sysname OUTPUT) AS BEGIN set noCount on -- Create a temporary table to store environment variables list Create table #EnvirnmentVariables (output varchar(1000)) -- populate the list Insert #EnvirnmentVariables exec master..xp_cmdshell 'set' -- select from the list the part after the = sign -- where Environment key parameter equals the part before the = sign Select @ResultValue = Substring (output, CharIndex ('=',output) +1, len(output) - CharIndex ('=',output)) from #EnvirnmentVariables where upper (@EnvVarName) = upper (Substring (output,1,CharIndex ('=',output)-1)) Drop table #EnvirnmentVariables set noCount off END GO
Note: I used a procedure rather than a user-defined function because temporary tables cannot by created and accessed in a UDF.
Here is an example of a call to the function:
-- Invoke the procedure USE pubs GO declare @outval sysname exec master.dbo.sp_GetEnvVarValue 'OS',@outval OUTPUT print @outval
This gives us the operating system name (Windows_NT in my case).