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).
ConclusionThe procedure I showed here can be used as a tool for getting the value of environment variables and solve the "parameter passing" problem that occurs sometimes when the "pieces" of code are not on the same platform or technology and communicate through environment variables set at operating system level.
About the authorEli Leiba (firstname.lastname@example.org) works at the Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies.
This was first published in February 2005