>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