FirebirdSQL logo

EXECUTE STATEMENT

Executes dynamically created SQL statements

Syntax
<execute_statement> ::= EXECUTE STATEMENT <argument>
  [<option> ...]
  [INTO <variables>];

<argument> ::= <paramless_stmt>
            | (<paramless_stmt>)
            | (<stmt_with_params>) (<param_values>)

<param_values> ::= <named_values> | <positional_values>

<named_values> ::= <named_value> [, <named_value> ...]

<named_value> ::= [EXCESS] paramname := <value_expr>

<positional_values> ::= <value_expr> [, <value_expr> ...]

<option> ::=
    WITH {AUTONOMOUS | COMMON} TRANSACTION
  | WITH CALLER PRIVILEGES
  | AS USER user
  | PASSWORD password
  | ROLE role
  | ON EXTERNAL [DATA SOURCE] <connection_string>

<connection_string> ::=
  !! See <filespec> in the CREATE DATABASE syntax !!

<variables> ::= [:]varname [, [:]varname ...]
Table 1. EXECUTE STATEMENT Statement Parameters
Argument Description

paramless_stmt

Literal string or variable containing a non-parameterized SQL query

stmt_with_params

Literal string or variable containing a parameterized SQL query

paramname

SQL query parameter name

value_expr

SQL expression resolving to a value

user

Username.It can be a string, CURRENT_USER or a string variable

password

Password.It can be a string or a string variable

role

Role.It can be a string, CURRENT_ROLE or a string variable

connection_string

Connection string.It can be a string literal or a string variable

varname

Variable

The statement EXECUTE STATEMENT takes a string parameter and executes it as if it were a DSQL statement.If the statement returns data, it can be passed to local variables by way of an INTO clause.

EXECUTE STATEMENT can only produce a single row of data.Statements producing multiple rows of data must be executed with [fblangref50-psql-forexec].

Parameterized Statements

You can use parameters — either named or positional — in the DSQL statement string.Each parameter must be assigned a value.

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