SET TERM ^^ ;
CREATE PROCEDURE SPS_ACCT_CUR_BAL (
V_ACCT_ID Integer)
returns (
R_CUR_BAL Numeric(15,2),
R_PRINC_BAL Numeric(15,2),
R_PRE_INT_BAL Numeric(15,2),
R_POST_INT_BAL Numeric(15,2),
R_FEE_BAL Numeric(15,2))
AS
DECLARE VARIABLE iCaseID SMALLINT;
DECLARE VARIABLE curCaseBal NUMERIC(15,2);
DECLARE VARIABLE curCasePrincBal NUMERIC(15,2);
DECLARE VARIABLE curCasePreIntBal NUMERIC(15,2);
DECLARE VARIABLE curCasePostIntBal NUMERIC(15,2);
DECLARE VARIABLE curCaseTotalFeeBal NUMERIC(15,2);
DECLARE VARIABLE curCaseFeeBal NUMERIC(15,2);
DECLARE VARIABLE curDebtFeeBal NUMERIC(15,2);

BEGIN
/* Default the return values */
R_CUR_BAL = 0.00;
R_PRINC_BAL = 0.00;
R_PRE_INT_BAL = 0.00;
R_POST_INT_BAL = 0.00;
R_FEE_BAL = 0.00;

FOR SELECT AC.CASE_ID
FROM ACCT_CASE AC
WHERE AC.ACCT_ID = :V_ACCT_ID
INTO :iCaseID DO
BEGIN
/* Determine this debts current balance */
curCaseBal = 0;

EXECUTE PROCEDURE SPS_CASE_CUR_BAL(V_ACCT_ID, iCaseID, NULL, 0)
RETURNING_VALUES curCaseBal, curCasePrincBal, curCasePreIntBal, curCasePostIntBal,
curCaseTotalFeeBal, curCaseFeeBal, curDebtFeeBal;

/* Current Balance */
R_CUR_BAL = R_CUR_BAL + curCaseBal;

/* Core Balances */
R_PRINC_BAL = R_PRINC_BAL + curCasePrincBal;
R_PRE_INT_BAL = R_PRE_INT_BAL + curCasePreIntBal;
R_POST_INT_BAL = R_POST_INT_BAL + curCasePostIntBal;
R_FEE_BAL = R_FEE_BAL + curCaseTotalFeeBal;
END
END
^^
SET TERM ; ^^

----- Original Message -----
From: mcnamara_gio
To: firebird-support@yahoogroups.com
Sent: Thursday, October 02, 2008 7:08 AM
Subject: {Disarmed} [firebird-support] Re: call stored procedure from another one.


--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 19:30 2/10/2008, you wrote:
>
> >Thanks it worked. But it is very slow.
>
> Using a SELECT procedure is not a sensible way to do this. Rewrite
the called procedure as an executable one and call it with EXECUTE
PROCEDURE .... RETURNING_VALUES(:outvar1, :outvar2, ....)
>
> .heLen
>
Can you show me an example how to do it?


.


[Non-text portions of this message have been removed]