| Subject | RE: [firebird-support] Select stored in a table |
|---|---|
| Author | Bogdan Mihalache |
| Post date | 2014-03-04T11:03:31Z |
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!
Bogdan
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvar
Sent: Tuesday, March 4, 2014 12:52 PM
To: firebird-support@yahoogroups.com
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 = :lcIdRamuraThings 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.
>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.um, 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 = ab.id
> Left Join nom_contracte_pret ac on aa.id_criteriu_pret = ac.id
> 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?
EXECUTE STATEMENT (SQL) (lcIdContract, lcIdRamura, lcIdSubramura, ldDela, ldPanala) Into :lnCantitate ;
(take a look at www.firebirdsql.org/refdocs/langrefupd25-psql-execstat.html for syntax & examples)
HTH,
Set