EXECUTE STATEMENT

Go Up to Procedures and Triggers

Description: Store procedure developers can now embed three variations of EXECUTE STATEMENT within their Stored Procedures. The variations depend on the number of rows returned from the EXECUTE STATEMENT command. The variations are: No rows or data returned, One row of data returned, and Any number of data rows returned.

No Rows or Data Returned

EXECUTE STATEMENT <statement>

Argument	Description
<statement>	A SQL statement returning no rows of data.

Examples:

CREATE PROCEDURE EXEC_STMT_NO_RET (proc_name varchar(20))
 AS
 DECLARE VARIABLE EMPNO INTEGER;
 DECLARE VARIABLE EXECSTMT VARCHAR(150);
 BEGIN
   SELECT MAX(EMP_NO) from EMPLOYEE into EMPNO;

EXECSTMT = 'EXECUTE PROCEDURE' || proc_name || '( ' || cast (EMPNO as varchar(10)) || ')';

EXECUTE STATEMENT EXECSTMT;

END

One Row of Data Returned

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

Argument	Description
<select-statement>	SQL statement returning one or no rows of data.
<var>	Valid procedure variable, the ":" is optional.

Example:

CREATE PROCEDURE EXEC_STMT_SINGLETON (TABLE_NAME VARCHAR(50))
 AS
 DECLARE VARIABLE MAXEMPNO INTEGER;
 BEGIN
 EXECUTE STATEMENT 'SELECT MAX(EMP_NO) FROM ' || TABLE_NAME INTO :MAXEMPNO;
 END

Any Number of Data Rows Returned

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

DO <compound-statement>

Argument	Description
<select-statement>	SQL statement returning one or zero rows of data.
<var>	Valid procedure variable. The `:` is optional.

Example:

CREATE PROCEDURE EXEC_STMT_ANY (TABLE_NAME VARCHAR(50), INT_FIELD INTEGER)
 RETURNS
 (INT_RETVAR INTEGER)
 AS
 DECLARE VARIABLE IFIELD INTEGER;
 BEGIN
  FOR EXECUTE STATEMENT
  'SELECT ' || INT_FIELD || ' FROM ' || TABLE_NAME INTO :IFIELD
  DO
    IF (IFIELD = 0) THEN
      INT_RETVAR = 0;
    ELSE
      INT_RETVAR = INT_RETVAR + IFIELD;
  SUSPEND;           
  END

Requirements and Constraints (EXECUTE STATEMENT)

There are constrains and peculiarities with using EXECUTE STATEMENT:

Starting with InterBase XE7 Update 1, there is a new requirement on FOR EXECUTE STATEMENT to match every item in the SELECT list with a corresponding item in the INTO list.
The Statement is "prepared" every time it is executed, which affects the performance of the Stored Procedure.
No checks are done on the statement when the procedure is created; dependency checks are not done when the procedure is created, also the checks for existence of tables or column names referred in the execute statement are not performed. All these checks are done at execute time and results in errors if an error condition occurs.
The feature can be used to perform DDL operations.
All statements are executed based on the privileges of the user executing the Stored Procedure.
SQL statements, "COMMIT:”, "COMMIT RETAIN", "ROLLBACK", "ROLLBACK RETAIN", and “CREATE DATABASE” are not supported with “EXECUTE STATEMENT”. These statements return the code isc_exec_stmt_disallow error.

Advance To:

FOR SELECT…DO