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