use "execute statement", but only if you really *must* do it this way.
Restrict the use of this to the most necessary cases, because you are actually paying by loss of control for that ... "comfort". > Hi all > I'm wondering whether its possible to pass a Where clause to a stored > procedure in Firebird 2.5 > In the past I have passed parameters to stored procedures but it > would be extremly useful to be able to build the Where clause at the > front end and then pass it to the stored procedure. > Heres an example:- > rather than comparing all fields:- > for select acct.id, acct.acc_name, acct.acc_number, > sum( accmstt.debit_amount ) sum_of_debit_amount, sum( > accmstt.credit_amount ) sum_of_credit_amount > from account_mthly_sum_totals_table accmstt inner join > accounts_table acct on (accmstt.account_table_id = acct.id) > where (account_type_table_id between 1 and 2) and > (payment_terms_strict_days between 0 and 9999999) and > (payment_terms_eom_days between 0 and 9999999) and > (payment_terms_notes like '%') and > (credit_limit between 0 and 9999999) > group by acct.id, acct.acc_name, acct.acc_number > order by acct.acc_number > into :op_id, :op_acc_name, :op_acc_number, > :op_debit, :op_credit do > begin > suspend; > end > the actual search may be just a simple:- > for select acct.id, acct.acc_name, acct.acc_number, > sum( accmstt.debit_amount ) sum_of_debit_amount, sum( > accmstt.credit_amount ) sum_of_credit_amount > from account_mthly_sum_totals_table accmstt inner join > accounts_table acct on (accmstt.account_table_id = acct.id) > where (account_type_table_id between 1 and 2) > group by acct.id, acct.acc_name, acct.acc_number > order by acct.acc_number > into :op_id, :op_acc_name, :op_acc_number, > :op_debit, :op_credit do > begin > suspend; > end > as the filters are optional to the user. > I was hoping to do something like:- > for select acct.id, acct.acc_name, acct.acc_number, > sum( accmstt.debit_amount ) sum_of_debit_amount, sum( > accmstt.credit_amount ) sum_of_credit_amount > from account_mthly_sum_totals_table accmstt inner join > accounts_table acct on (accmstt.account_table_id = acct.id) > where :ip_where_clause > group by acct.id, acct.acc_name, acct.acc_number > order by acct.acc_number > into :op_id, :op_acc_name, :op_acc_number, > :op_debit, :op_credit do > begin > suspend; > end > and the :ip_where_clause is the input value for the where clause. > tia > ------------------------------------ > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Visit http://www.firebirdsql.org and click the Resources item > on the main (top) menu. Try Knowledgebase and FAQ links ! > Also search the knowledgebases at http://www.ibphoenix.com > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > Yahoo! Groups Links mit freundlichen Grüßen, André Knappstein EDV und Controlling ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ beta Eigenheim- und Grundstücksverwertungsgesellschaft mbH Hafenweg 4 59192 Bergkamen-Rünthe Telefon: +49 2389 9240 140 Telefax: +49 2389 9240 150 e-mail: [email protected] Amtsgericht Hamm Nr. B 420 Geschäftsführer: Achim Krähling, Dirk Salewski und Matthias Steinhaus USt-IDNr.: DE 125215402
