Andrew,

> I'm wondering whether its possible to pass a Where clause to a stored
> procedure in Firebird 2.5
>
> 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

I think you may be using Stored Procedure when you actually want to use either:

A View:

CREATE VIEW Account_Totals
AS (
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)
group by acct.id, acct.acc_name, acct.acc_number
)

Or, a couple of COMPUTED columns

ALTER TABLE Accounts_Table
ADD sum_of_debit_amount COMPUTED BY (SELECT COALESCE( sum( accmstt.debit_amount), 0) FROM account_mthly_sum_totals_table accmstt WHERE (accmstt.account_table_id = ID),
ADD sum_of_credit_amount COMPUTED BY (SELECT COALESCE( sum(accmstt.credit_amount), 0) FROM account_mthly_sum_totals_table accmstt WHERE (accmstt.account_table_id = ID)
;

Either approach would provide significantly more flexibility compared to a Procedure.


Sean