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.
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.
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
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
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.