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