FirebirdSQL logo
Special Rules for Parameterized Statements
  1. Named and positional parameters cannot be mixed in one query

  2. 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.

  3. 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

  4. Each named parameter must be prefixed by a colon (‘:’) in the statement string itself, but not when the parameter is assigned a value

  5. Positional parameters must be assigned their values in the same order as they appear in the query text

  6. The assignment operator for parameters is the special operator “:=”, similar to the assignment operator in Pascal

  7. Each named parameter can be used in the statement more than once, but its value must be assigned only once

  8. With positional parameters, the number of assigned values must match the number of parameter placeholders (question marks) in the statement exactly

  9. 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
  1. 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;
  2. 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;
  3. 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