Special Rules For Parameterized Statements

Data Manipulation (DML) StatementsBuilt-in Scalar Functions

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)

Examples of `EXECUTE STATEMENT` with parameters

With named parameters:

...
DECLARE license_num VARCHAR(15);
DECLARE connect_string VARCHAR (100);
DECLARE stmt VARCHAR (100) =
  'SELECT license '
  'FROM cars '
  'WHERE driver = :driver AND location = :loc';
BEGIN
  -- ...
  EXECUTE STATEMENT (stmt)
    (driver := current_driver,
     loc := current_location)
  ON EXTERNAL connect_string
  INTO license_num;

The same code with positional parameters:

DECLARE license_num VARCHAR (15);
DECLARE connect_string VARCHAR (100);
DECLARE stmt VARCHAR (100) =
  'SELECT license '
  'FROM cars '
  'WHERE driver = ? AND location = ?';
BEGIN
  -- ...
  EXECUTE STATEMENT (stmt)
    (current_driver, current_location)
  ON EXTERNAL connect_string
  INTO license_num;

Use of EXCESS to allow named parameters to be unused (note: this is a FOR EXECUTE STATEMENT):

CREATE PROCEDURE P_EXCESS (A_ID INT, A_TRAN INT = NULL, A_CONN INT = NULL)
  RETURNS (ID INT, TRAN INT, CONN INT)
AS
DECLARE S VARCHAR(255) = 'SELECT * FROM TTT WHERE ID = :ID';
DECLARE W VARCHAR(255) = '';
BEGIN
  IF (A_TRAN IS NOT NULL)
  THEN W = W || ' AND TRAN = :a';

  IF (A_CONN IS NOT NULL)
  THEN W = W || ' AND CONN = :b';

  IF (W <> '')
  THEN S = S || W;

  -- could raise error if TRAN or CONN is null
  -- FOR EXECUTE STATEMENT (:S) (a := :A_TRAN, b := A_CONN, id := A_ID)

  -- OK in all cases
  FOR EXECUTE STATEMENT (:S) (EXCESS a := :A_TRAN, EXCESS b := A_CONN, id := A_ID)
    INTO :ID, :TRAN, :CONN
      DO SUSPEND;
END

Special Rules For Parameterized Statements

Special Rules for Parameterized Statements

Examples of EXECUTE STATEMENT with parameters

Examples of `EXECUTE STATEMENT` with parameters