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.
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:
Before executing the entire SQL Script the user have to enter all variables in the script:
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.