Parametrized load queries and scripts

In the Staging database, you can use parameters in load queries and scripts. In the Integration database, you can use parameters in load queries.

In addition, if you use custom settings in load queries, those settings are automatically translated to SQL parameters.

Parameters can be of these data types:

  • Text
  • Number
  • Date/Time
  • Boolean

Reference single-value parameters with @[parameter_name].

Reference multi-value parameters with @@[parameter_name]. Values in multiple-value parameters must be separated by the pipe (|) character.

Parametrized load query

You have a table called Drink_Sales in the Staging database. It holds sales of several drinks. When you load data, you specify which drink is referenced by the parameter.

  1. Create a load query called LoadDrinkSales.
  2. Create a parameter called pProduct.
  3. Specify your load query with this syntax:
    Select * from Drink_Sales where Drink=@pProduct
    If the parameter is multi-value, specify: 
    Select * from Drink_Sales where Drink IN (@@pProduct)
    .

    This is the same syntax as is used in Application Studio relational lists.

  4. Click Preview.
  5. In the Load Query Parameters dialog box, specify the values for the parameter. To specify multiple values, separate them with the pipe (|) character.
Note: If a parameter is not referenced in a load query or script, then you can leave it empty. But if a parameter is referenced, then you must provide a value. Leaving a used parameter empty does not send NULL or an empty string.

Parametrized script

This example references a parameter to delete a row from the Drink_Sales table.

  1. Create a script called DeleteDrinkSales.
  2. Create a parameter called pProduct.
  3. Specify your script with this syntax:
    Delete from Drink_Sales where Drink=@pProduct