Environment & System Variables - Embedding Parameters in SQL Code

Introduction to Environment Variables

Environment Variables are user-defined parameters that can be embedded in SQL code. These variables are specific to each environment (and can vary between different environments). When code that uses environment variables is executed, Combine will prompt you to validate or update the value of each variable. Then, Combine will replace each Environment Variable with the appropriate value in the code and proceed to execute the code against the appropriate target databases. Environment Variables are supported in SQL scripts in code packages, in individual (i.e., non-package) scripts in the editor, and in the Query used by Dynamic Containers.

Example: A stored procedure is created in the Development environment and selects from a table called "utbMyTable". In the Development environment, the owner of the table is "dbo", however the table owner in the Production environment is "User1". Using Environment Variables, you can write a single script to create the stored procedure in Development and in Production, without modifying the code before it is executed: First, define <$TableOwner$> as an Environment Variable in the Development and Production environments in the Container Manager. Then, assign the value "dbo" to this variable under the Development environment and "User1" under the Production Environment. Next, replace the reference to the table owner in your SQL script, for example:

-- Replace the code
SELECT Column1, Column2
FROM dbo.utbMyTable

-- With this code
SELECT Column1, Column2
FROM <$TableOwner$>.utbMyTable

Finally, when the code is executed against the Development environment, Combine will automatically replace the Environment Variable <$TableOwner$> with "dbo". Similarly, when the code is executed against the Production environment, this value will be replaced with "User1" before it is deployed.

Environment variables can be used when running a script against a single database (in which case the assigned values are those under the Active Environment), when running a script against multiple databases, or when running a code package. In the last two cases, variable values are those defined under each environment, and Combine also allows you to validate and update the values of all Environment Variables before any code is executed against target databases (see Changing the Value of Variables in Run-Time for more information).

Note: Although the example above shows the use of a single Environment Variable in a script, each script can contain several variables. Furthermore, for code packages, you can use Environment Variables is all package scripts as needed for your code release.

© 2001-2018 JNetDirect, Inc. All Rights Reserved.