<< EXECUTE PROCEDURE | FB 2.1 Language Reference | EXIT >>
<< EXECUTE PROCEDURE | FB 2.0 Language Reference | EXIT >>

EXECUTE STATEMENT

Available in: PSQL

Added in: 1.5

Description

EXECUTE STATEMENT takes a single string argument and executes it as if it had been submitted as a DSQL statement. The exact syntax depends on the number of data rows that the supplied statement may return.

No data returned

This form is used with INSERT, UPDATE, DELETE and EXECUTE PROCEDURE statements that return no data.

Syntax

 EXECUTE STATEMENT <statement>

 <statement> ::= An SQL statement returning no data.

Example

 create procedure DynamicSampleOne (ProcName varchar(100))
 as
 declare variable stmt varchar(1024);
 declare variable param int;
 begin
    select min(SomeField) from SomeTable into param;
    stmt = 'execute procedure '
           || ProcName
           || '('
           || cast(param as varchar(20))
           || ')';
    execute statement stmt;
 end

Warning: Although this form of EXECUTE STATEMENT can also be used with all kinds of DDL strings (except CREATE/DROP DATABASE), it is generally very, very unwise to use this trick in order to circumvent the no-DDL rule in PSQL.

One row of data returned

This form is used with singleton SELECT statements.

Syntax

 EXECUTE STATEMENT <select-statement> INTO <var> [, <var> ...]

 <select-statement> ::= An SQL statement returning at most one row of data.
 <var>              ::= A PSQL variable, optionally preceded by “:”

Example

 create procedure DynamicSampleTwo (TableName varchar(100))
 as
 declare variable param int;
 begin
    execute statement
       'select max(CheckField) from ' || TableName into :param;
    if (param > 100) then
       exception Ex_Overflow 'Overflow in ' || TableName;
 end

Any number of data rows returned

This form – analogous to FOR SELECT ... DO – is used with SELECT statements that may return a multi-row dataset.

Syntax

 FOR EXECUTE STATEMENT <select-statement> INTO <var> [, <var> ...]
   DO <compound-statement>

 <select-statement> ::= Any SELECT statement.
 <var>              ::= A PSQL variable, optionally preceded by “:”

Example

 create procedure DynamicSampleThree
    ( TextField varchar(100),
      TableName varchar(100) )
 returns
    ( LongLine varchar(32000) )
 as
 declare variable Chunk varchar(100); 
 begin
    Chunk = '';
    for execute statement
      'select ' || TextField || ' from ' || TableName into :Chunk
    do
      if (Chunk is not null) then
        LongLine = LongLine || Chunk || ' ';
    suspend;
 end

Caveats with EXECUTE STATEMENT

1. There is no way to validate the syntax of the enclosed statement.
2. There are no dependency checks to discover whether tables or columns have been dropped.
3. Operations will be slow because the embedded statement has to be prepared every time it is executed.
4. The argument string cannot contain any parameters. All variable substitution into the static part of the DSQL statement should be performed before EXECUTE STATEMENT is called.
5. Return values are strictly checked for data type in order to avoid unpredictable type-casting exceptions. For example, the string '1234' would convert to an integer, 1234, but 'abc' would give a conversion error.
6. The submitted DSQL statement is always executed with the privileges of the current user. Privileges granted to the trigger or stored procedure that contains the EXECUTE STATEMENT statement are not in effect while the DSQL statement runs.

All in all, this feature is intended only for very cautious use and you should always take the above factors into account. Bottom line: use EXECUTE STATEMENT only when other methods are impossible, or perform even worse than EXECUTE STATEMENT.

See also:
IBEBlock: EXECUTE STATEMENT
Firebird 2.5 Release Notes: New extensions to EXECUTE STATEMENT

back to top of page
<< EXECUTE PROCEDURE | FB 2.1 Language Reference | EXIT >>
<< EXECUTE PROCEDURE | FB 2.0 Language Reference | EXIT >>