Hi Helen and Alan

Thanks a lot for your answer for this query. I have been able to
successfully SELECT and get the return value from it.

Thanks.

--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 06:36 PM 22/11/2007, you wrote:
> >Hi all
> >
> >Thanks to Helen help in another thread I am onto writing SPs in
> >FireBird. Unfortunately my simplest of SP does not return any value
> >when I execute it as follows:
> >SELECT * FROM NarrAcctOpBal
> >
> >The SP is as follows:
> >SET TERM ^ ;
>
> >CREATE PROCEDURE NARRACCTOPBAL
> >RETURNS (
> > MNARRATION Varchar(32000) )
> >AS
> >BEGIN
> > mNarration = 'Opening Balance';
> >END^
>
>
> >SET TERM ; ^
> >
> >Please advise on what I need to do more.
>
> There are SELECTABLE SPs and EXECUTABLE SPs. What you wrote here is
an executable SP. That means it won't return anything from a SELECT.
You need a SUSPEND statement to make it into a selectable statement:
>
> CREATE PROCEDURE NARRACCTOPBAL
> RETURNS (
> MNARRATION Varchar(32000) )
> AS
> BEGIN
> mNarration = 'Opening Balance';
> SUSPEND;
> END^
>
>
> >Down the line I will have a
> >parameter to this SP which provides an PK that needs to be looked up.
> > I intend to do a SELECT and create a string using the PK parameter in
> >the WHERE and return this string to be used in my other SELECTs.
>
> Unless the PK is CHAR() or VARCHAR(), that is just plain silly.
Preparing the query returns the data types of everything to the
client, whereupon you just plug the PK data directly into the input
parameter. Suppose the PK is BIGINT, then your SSP would be something
like this:
>
> create procedure blah (
> PK BigInt)
> returns (
> f1 varchar(99),
> f2 timestamp)
> as
> begin
> FOR
> select f1, f2 from aTable
> where pk = :PK
> into :f1, :f2
> DO
> SUSPEND;
> end
>
> Chapter 3 of the IB6 beta docs Language Reference (LangRef.pdf)
gives you many of the "basics" of PSQL. You can also search the
IBPhoenix website for several papers on various aspects of it. And
you must arm yourself with the release notes for Firebird 1.5.4 and
2.0.3 for the many additions to PSQL in the Firebird era (get these
via the Documentation Index at Firebird website).
>
> Of course, if you own The Firebird Book, there's a whole section of
the book (Part VII, 5 chapters) dedicated to it...
>
> ./heLen
>