Parametric Queries

When you catalog a SQL query, you have the option of making it parametric. Parametric SQL Queries accept arguments at runtime.

When you define the SQL statement of a parametric query, you can insert special markers in the place of actual values. Studio then generates a component that accepts arguments to fill into the placeholders when invoked.

This allows you specialize the query according to values that are determined at runtime, instead of design time when you design and catalog the query.

Syntax

To use parametric markers in your SQL statement, use the following syntax: $name:type, where "name" is the name of the parameter and "type" is the SQL type of the parameter value.

Each parameter defined by the query becomes an argument to the constructor of the generated query component.

Example

Example: Here's a simple parametric query for a hypothetical component named EmployeesByDepartment:

  SELECT * from EMPLOYEE WHERE deptNumber = $dept:VARCHAR

When using the query component, you pass the value for the "dept" placeholder to the constructor, as follows:

  for each hrEmployee in EmployeesByDepartment( dept: "HR1" )
  do
     // ...
  end