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:

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:

This...

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT Database Management and Administration Password cracking tools for SQL Server Using traces in SQL Server Profiler Meet compliance requirements with improved database security practices Hardening the network and OS for SQL Server security Securing the server and database in SQL Server How SQL Server 2008 components impact SharePoint implementations Troubleshooting Distributed Transaction Coordinator errors in SQL Server Achieving high availability and disaster recovery with SharePoint databases Clearing the Windows page file and its effect on server performance Deploying a SQL Server virtual appliance for Microsoft Hyper-V SQL/Transact SQL (T-SQL) Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server SQL language crash course (just enough to be dangerous) Working with IntelliSense in SQL Server 2008 Management Studio SQL Server Mailbag: Stored procedures, triggers and SSRS reports Working with sparse columns in SQL Server 2008 Determining the source of full transaction logs in SQL Server New GROUP BY option provides better data control in SQL Server 2008 Using the OPENROWSET function in SQL Server Loading data files with SQL Server's BULK INSERT statement Importing and exporting bulk data with SQL Server's bcp utility SQL/Transact SQL (T-SQL) Research

RELATED GLOSSARY TERMS Terms from Whatis.com − the technology online dictionary ACID  (SearchSQLServer.com) Collaboration Data Objects  (SearchSQLServer.com) commit  (SearchSQLServer.com) container  (SearchSQLServer.com) DAO  (SearchSQLServer.com) fetch  (SearchSQLServer.com) OLE DB  (SearchSQLServer.com) query  (SearchSQLServer.com) SQL  (SearchSQLServer.com) T-SQL  (SearchSQLServer.com)

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

gives us the operating system name (Windows_NT in my case).

Conclusion

About the author