EXECUTE STATEMENT

No data returned
One row of data returned
Any number of data rows returned
Caveats with 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

There is no way to validate the syntax of the enclosed statement.
There are no dependency checks to discover whether tables or columns have been dropped.
Operations will be slow because the embedded statement has to be prepared every time it is executed.
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.
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.
The submitted DSQL statement is always executed with the privileges of the current user. Privileges granted to the trigger or SP 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.