Subject RE: [firebird-support] Select stored in a table
Author Bogdan Mihalache

Meanwhile i have found that link, but i was wondering if there is a way to pass the parameters in a variable or something like that.

I have made a new fiels in the table that stores the sql statement, and put in there the variables like this :

-          lcIdContract := :lcIdContract, lcIdRamura := :lcIdRamura, lcIdSubramura := :lcIdSubRamura, ldDela := :ldDela, ldPanala := :ldPanala

My problem is that there is more than one sql statement there, with different parameters name and count.


i`ve tried “execute statement (sql) (parametes)” since there it says that i can use an other expresion (When a statement has parameters, it must be placed in parentheses when EXECUTE STATEMENT is called, regardless whether it is given directly as a string, as a variable name, or by another expression.) but i think that i don`t really understant how this works.


As a question is there an aproximate way to use this : execute statement (sql) (parametes) ? or something similar to it? Has anyone tried something like this? What was your aproach?


Thank you verry much!









From: [] On Behalf Of Svein Erling Tysvar
Sent: Tuesday, March 4, 2014 12:52 PM
Subject: RE: [firebird-support] Select stored in a table



>I have this Sql statement strored in a table :

>Select sum(secunde_fact)  as cantitate  From agenda Where id_contract = :lcIdContract and  id_ramura = :lcIdRamura
>And id_subramura = :lcIdSubramura And (agenda.data_factura between :ldDela And :ldPanala )   And facturabil = 1 and deleted = 0
>I wanna used in a stored procedure, like this :
> lcIdClient     = id_client ;
>          lcIdContract   = id_contract ;
>            lcIdRamura     = id_ramura ;
>            lcIdSubramura  = id_subramura ;
>            ldDela         = lcDela ;
>            ldPanala       = lcPanala  ;
>            lnAn           = Extract (Year From ldDela) ;
>            lnLuna         = Extract (Month From ldDela)  ;
>            For Select, ab.sql, aa.val1, aa.val2, aa.val3, aa.val4, aa.int_start,
>                    aa.int1, aa.int2, aa.int3, aa.int4, aa.id_criteriu_pret, aa.id_pret_um,
>                    aa.felpret, aa.ispretfix, ac.id_subramura
>                From contracte_det_pret aa
>                Left Join nom_contracte_pret_um ab on aa.id_pret_um         =
>                Left Join nom_contracte_pret    ac on aa.id_criteriu_pret   =
>                Where aa.id_contract =  :lcIdContract And ac.id_subramura = :lcIdSubramura
>                Into :um, :sql, :val1, :val2,  :val3, :val4, :int_start,
>                    :int1, :int2, :int3, :int4, :lcIdCriteriuPret, :lcIdPretUm,
>                    :felpret, :ispretfix, :lcIdSubramura
>               do
>               begin
>                 if (lower(:felpret) = 'pretunitar')
>                  then
>                    begin
>                      Execute Statement Sql Into :lnCantitate ;
>                      lnCantitate = coalesce(:lnCantitate, 0) ;
>                      valoare = lnCantitate *  val1 ;
>                      suspend;
>                    end
>I am using IBExpert as a tool to view my database. In debug mode it works just fine. But if i hit play,
>i`m receivind an error, that he can`t fiind column lcIdContract, at this line(when he is trying to
>execute the sql statement) :    Execute Statement Sql Into :lnCantitate ;
>If i remove the variables(:lcIdContract, :lc IdRamura, etc.)  from the sql statement, that is stored in
>the table, it`s working, but I really need to keep them, and keep the sql in the table.  Does anyone has
>a solution to my problem?

Things are slightly more complex with parameters, Bogdan, though just slightly: You need to pass the parameters into your execute statement (think of execute statement as self contained, with no knowledge of things outside of itself), i.e.

EXECUTE STATEMENT (SQL) (lcIdContract, lcIdRamura, lcIdSubramura, ldDela, ldPanala) Into :lnCantitate ;

(take a look at for syntax & examples)