Thanks again Helen and Alan for both of you pointing me in the right
direction. Though for this SP I have not been able to use it in the
SELECT statement like below:

SELECT NarrAcctOpBal2.lmNarration, mAccounts.cCity
FROM mAccounts
JOIN NarrAcctOpBal2(mAccounts.iID) on NarrAcctOpBal2.liID =
mAccounts.iID

How can I send the mAccounts.iID, let SP do the processing (not much
yet) and return lmNarration for each liID that matches with iID of
mAccounts (see my untested modified SP below), so I get a result set like,

Opening Balance of A.B.C. Ltd. A/c., Amsterdam
Opening Balance of D.E.F. LLC A/c., London
Opening Balance of X.Y.Z. Co. A/c., New York

Thanks again and regards
Bhavbhuti


CREATE PROCEDURE NARRACCTOPBAL2 (
IID Integer )
RETURNS (
LIID Integer,
LMNARRATION Varchar(16) ) /* !!!!!!!!! */
AS
BEGIN
FOR
SELECT mAccounts.iID, 'Opening Balance of ' || mAccouts.cName
FROM MACCOUNTS
where maccounts.iID = :iID
INTO :liid, :lmnarration
DO
SUSPEND;
END

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 06:42 PM 22/11/2007, you wrote:
> >Hi all
> >
> >I have another SP gearing towards what I intend to return from SP. I
> >want to use it in a SELECT as follows:
> >
> >SELECT mAccounts.*, NarrAcctOpBal2.mNarration
> > FROM mAccounts
> > JOIN NARRACCTOPBAL2(mAccounts.iID) on NarrAcctOpBal2.iID =
> >mAccounts.iID
> >
> >My SP which errors and cannot be created is as follows:
> >SET TERM ^ ;
> >CREATE PROCEDURE NARRACCTOPBAL2 (
> > IID Integer )
> >RETURNS (
> > LIID Integer,
> > LMNARRATION Varchar(32000) )
> >AS
> >BEGIN
> > SELECT mAccounts.iID as liID, 'Opening Balance' AS lmNarration
> > FROM MACCOUNTS
> > where maccounts.iID = :iID;
> >END^
> >SET TERM ; ^
> CREATE PROCEDURE NARRACCTOPBAL2 (
> IID Integer )
> RETURNS (
> LIID Integer,
> LMNARRATION Varchar(16) ) /* !!!!!!!!! */
> AS
> BEGIN
> FOR
> SELECT mAccounts.iID, 'Opening Balance'
> FROM MACCOUNTS
> where maccounts.iID = :iID
> INTO :liid, :lmnarration
> DO
> SUSPEND;
> END
>
> ./hb
>