Problem solve Get help with specific problems with your technologies, process and projects.

SQL Server parameter passing fix

Options for fixing SQL Server parameter passing problems, such as setting an environment variable in the windows operating system, are offered in this tip.

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).

Conclusion

The 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 author

Eli Leiba (iecdba@hotmail.com) 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 last published in February 2005

Dig Deeper on SQL-Transact SQL (T-SQL)

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close