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