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
