Submitted by: @livius2

Is related to CORE4736

Votes: 2

Will be good to see a way to build dynamic list of parameters for execute statement at procedure run-time
now we have only possibility to specify parameters known at procedure creation time like

EXECUTE STATEMENT (VAR_SQL) (PARAM_X := aaa, PARAM_Y := ....)

but it is impossible to build params list at runtime - which "violates" concept of EXECUTE STATEMENT for dynamically created queries

Will be good to see the way to specify dynamic list of parameters build at run-time.
The concept is in example pseudo-code

SET TERM ^ ;
CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER)
AS
DECLARE VARIABLE VAR_SQL VARCHAR(1000);
DECLARE VARIABLE VAR_BBB INTEGER;
DECLARE VARIABLE VAR_NR INTEGER;
DECLARE VARIABLE VAR_PARAM_NAME VARCAHR(10);
BEGIN
VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID ';
VAR_NR = 1;
FOR SELECT
BBB
FROM
TABLEZ
WHERE ID=:ID
INTO :VAR_BBB
DO
BEGIN
/* DO SOME CALCULATION ON VAR_BBB */
VAR_PARAM_NAME = 'ID' || VAR_NR;
VAR_SQL = VAR_SQL || ' OR ID=:' || VAR_PARAM_NAME;

    PARAMS\.ByNAME\[VAR\_PARAM\_NAME\] := VAR\_BBB; <\-\- here we create param in virtual space "PARAMS" \- quite same like NEW\.FIELD, OLD\.FIELD work \- but parameter is created at request not at start of proc
    /\* may be it is also possible for unnamed params \- PARAMS\.ByPosition\[4\] 4 treated as name with internal prefix to prevent allocation of parameters form 1 to 9999 if we specify ByPosition\[9999\] ;\-\)
   \*/
  VAR\_NR = VAR\_NR \+ 1;
END

EXECUTE STATEMENT (VAR_SQL) (PARAMS) <----- Here we specify parameters list
INTO :SUM_AAA;

SUSPEND;
END^
SET TERM ; ^

this feature is important from performance POV and server resources POV for queries like this:

SELECT (SELECT P.SUM_AAA FROM MY_PROC_CALC_EXEC_PARAM(T.FIELD_ID) P) FROM TABLEX T

without parameters, for every record returned from TABLEX, stored procedure is executed and query is prepared
prepare distinct queries for every record is time consuming process
but with parameters this will be very fast only few distinct prepared queries

i compare times for real example
where i put in stored proc execute statement with and without parameters
and for query with 100000 records i got times:
15 sec with parameters
42 sec plain text passed to execute statement - for every record query was prepared by server which consume time

PS.
old form without (PARAMS) should work as previously
like EXECUTE STATEMENT (VAR_SQL) (ID1 := something, ID2:= something)

====== Test Details ======

See test for CORE5658