Using Parameters in Queries

Last Updated On

We use the following example to explain how to use a parameter within a probes query that finally gets executed on the datasource.

SELECT COUNT(*) FROM Sales WHERE MonthNumber = {{Month:1}}

In this example, the Parameter “Month” gets used as a filter. The parameter-placeholder represented within two curly brackets gets replaced by the actual value of the parameter.

  • Use double curly brackets {{ … }} in any probe query to use a parameter at this exact position.
  • Right after the opening double curly brackets, the name of the Parameter follows. Do not use apostrophes or any other quote-characters.
  • Separated by a colon a default value follows. This default value is used in the case a parameter has no value. Usually this only happens when you push the “Validate” button within the probe-editor. Because the parameter wasn’t set in this case, we use a default-value to run the query.
  • Depending on the data type and the query-syntax, you need to tell BiG EVAL how to handle the parameter-value within that query. See the different possibilities below.
NumericWhen using a parameter with a numeric value, you don’t need to specify that explicitly.
SELECT COUNT(*) FROM Sales WHERE MonthNumber = {{Month:1}}
TextWhen using a text or character value, specify it as STRING like following. This is needed, because strings are handled differently in each datasource syntax.
SELECT COUNT(*) FROM Customers WHERE Name = {{CustomerName :"Bolt" as STRING}}
DateUse the format DD.MM.YYYY as the default value within double quotes and specify the parameter as DATE.
SELECT COUNT(*) FROM Sales WHERE OrderDate = {{OrderDate :"17.3.2017" as DATE}}
Date and TimeUse the format DD.MM.YYYY HH:MI:SS.SSS as the default value within double quotes and specify the parameter as DATETIME.
SELECT COUNT(*) FROM Sales WHERE OrderTimestamp = {{OrderDateTime: "17.3.2017 13:00:12.123" as DATETIME}}
ObjectnamesYou can use a parameter to dynamically set object-names within your datasource query. This can be used to change the table-name or column-names dynamically for example. Using the OBJECTNAME datatype, BiG EVAL adds the syntax-specifiers for object-names (e.g. [..].[..]) depending on the datasource syntax.
SELECT COUNT(*) FROM {{TableName: "dbo.Sales" as OBJECTNAME}}
PlaintextUse the PLAINTEXT datatype to write out the parameter-value 1:1 without any change. This can be used when the parameter-value contains the full datasource query that was prepared in the script for example, or when BiG EVAL doesn’t know the syntax of the datasource query technology.
SELECT COUNT(*) FROM {{TableName: "[dbo].[Sales]" as PLAINTEXT}}
Table of Contents