Special Rules for Parameterized Statements
-
Named and positional parameters cannot be mixed in one query
-
Each parameter must be used in the statement text.
To relax this rule, named parameters can be prefixed with the keyword
EXCESS
to indicate that the parameter may be absent from the statement text.This option is useful for dynamically generated statements that conditionally include or exclude certain parameters. -
If the statement has parameters, they must be enclosed in parentheses when
EXECUTE STATEMENT
is called, regardless of whether they come directly as strings, as variable names or as expressions -
Each named parameter must be prefixed by a colon (‘
:
’) in the statement string itself, but not when the parameter is assigned a value -
Positional parameters must be assigned their values in the same order as they appear in the query text
-
The assignment operator for parameters is the special operator “
:=
”, similar to the assignment operator in Pascal -
Each named parameter can be used in the statement more than once, but its value must be assigned only once
-
With positional parameters, the number of assigned values must match the number of parameter placeholders (question marks) in the statement exactly
-
A named parameter in the statement text can only be a regular identifier (it cannot be a quoted identifier)