> The procedure is returning only one person info,
> which is the last person in the table.

... because you call SUSPEND only once, after the loop ends. It should be

FOR SELECT ...
DO
BEGIN
EXECUTE PROCEDURE ...;
SUSPEND;
END

Ivan

----- Original Message -----
From: "Muthu Annamalai" <pearlamerica@...>
To: <firebird-support@yahoogroups.com>
Sent: Sunday, May 02, 2004 6:03 PM
Subject: [firebird-support] Need Help in SP to return multiple rows


> The puropose of the below Stored Procedure is to return all person
> information associated with a particular customerid. Customernumber
> is input parameter.The procedure is returning only one person info,
> which is the last person in the table.
>
> CREATE PROCEDURE GETCUSTOMERPERSONS(
> CUSTOMERNUMBER VARCHAR(20))
> RETURNS (
> TITLE VARCHAR(10),FIRSTNAME VARCHAR(25),LASTNAME VARCHAR(25),
> STREET VARCHAR(50), CITY VARCHAR(50), STATE VARCHAR(25), ZIP
> VARCHAR(50),PHONE CHAR(15),CELL CHAR(15),EMAIL VARCHAR(50))
> AS
> DECLARE VARIABLE PERSONID INTEGER;
> begin
>
> FOR
> SELECT PERSONID FROM CUSTOMERCONTACT,CUSTOMER
> WHERE CUSTOMERID = CUSTOMER.ID AND CUSTOMER.CUSTOMERNUMBER
> =:CUSTOMERNUMBER INTO :PERSONID
>
> DO
>
> EXECUTE PROCEDURE GETPERSON(:PERSONID)
> RETURNING_VALUES
> ( :TITLE, :FIRSTNAME, :LASTNAME, :STREET, :CITY, :STATE , :ZIP , :PH
> ONE , :CELL , :EMAIL );
>
> SUSPEND;
> end
>
> Regards,
>
> Muthu Annamalai