SQL Server parameter passing fix

SQL Server parameter passing fix

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

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
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

This was first published in February 2005

        (@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.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top