Execute parameterized SQL scripts

Caché Monitor can execute parameterized SQL queries. This will help writing complex scripts with variables, the variables let Caché Monitor prompt you for the values when the SQL is executed. This is handy if you perform the same statements again and again and just wanting to pass new data in.

For example:

SELECT * FROM CINEMA.Film 
WHERE CINEMA.Film.Category=$$MyVAR_001$$

When executing the above query, Caché Monitor will prompt the user for input the variable MyVAR_001. The SQL Editor replace the value of $$MyVAR_001$$ with the value entered by the user. The entered value will be substituted for each occurrence in the SQL Script.

The first step is to activate this feature! By default the variable substitution for sql script is disabled. It has to be enabled by pressing the toolbar, please see this screenshot:

parameterized-SQL

 

Before executing the entire SQL Script the user have to enter all variables in the script:

parameterized-SQL_all_vals

On the message panel you find the sql statements send to the database.

Syntax of variables and using default values

The variable substitution in SQL scripts supports also setting a default value.

Variable Syntax ist

$$ VarName [ || VarValue]$$
  • VarName = Required. The variable name appears in the substitution dialog. The entered value will be applied to all variables of that name.
  • VarValue = Optional. The default value that appears in the substitution dialog.

 

The variable format supports setting a default value as in the following examples:

SELECT *
 FROM CINEMA.Film
 WHERE CINEMA.Film.Category = $$MyVAR_002||4$$
SELECT *
 FROM CINEMA.FilmCategory
 WHERE CINEMA.FilmCategory.CategoryName = $$MyVAR_003||'Action'$$

 

The variable substitution handles also complex default values like e.g. Sub selects.