[firebird-support] How to pass a where clause to a stored procedure

[andrew.pailtho...@btinternet.com]

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