EXECUTE STATEMENT
Go Up to Procedures and Triggers
Contents
Embedding a variation of EXECUTE STATEMENTS within a Stored Procedure.
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 |
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.
See Also
For information on error messages added with the function, see : “Error Codes and Messages”