>3. when i change proc to use execute statement with parameters > >SET TERM ^ ; >CREATE PROCEDURE MY_PROC_CALC_EXEC_PARAM(ID INTEGER) RETURNS(SUM_AAA INTEGER) >AS >DECLARE VARIABLE VAR_SQL VARCHAR(1000); >BEGIN > VAR_SQL = 'SELECT SUM(AAA) FROM TABLEY WHERE ID=:ID'; > EXECUTE STATEMENT (VAR_SQL) (ID := :ID) INTO :SUM_AAA; > SUSPEND; >END^ >SET TERM ; ^ > >and run query >SELECT (SELECT P.SUM_AAA FROM MY_PROC_CALC_EXEC_PARAM(T.FIELD_ID) P) FROM >TABLEX T > >than select is prepared only once - ok :)
I'm impressed if Firebird understands that VAR_SQL doesn't change between each iteration so that preparing once is possible! >4. but how to change this sample code to use paramaters and do not prepare >statement on every record? > >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; >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_SQL = VAR_SQL || ' OR ID=:ID' || VAR_NR; > How to add here parameter to list of parameters for below execute >statement? > END > > EXECUTE STATEMENT (VAR_SQL) (ID := :ID, ID1, ID2, ID3 ...) <----- How to >specify parameters dynamically? > INTO :SUM_AAA; > > SUSPEND; >END^ >SET TERM ; ^ > > >Is this somehow possible? Or should i add this to the Firebird bug tracker as >new feature request? >This is important from performance point of view. Here you change the SQL for each iteration, hence I cannot see how it would be possible to prepare only once. What you could try, is to use a temporary table, change your statement to: SELECT SUM(Y.AAA) FROM TABLEY Y JOIN TMP_TABLE T ON Y.ID = T.ID and in your loop do INSERT INTO TMP_TABLE(ID) VALUES(:VAR_NR) HTH, Set
