At 04:03 PM 2/05/2004 +0000, you wrote:

>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

1) ambiguous join syntax
2) missing begin..end


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 cc
join CUSTOMER c
on cc.CUSTOMERID = c.ID
where c.CUSTOMERNUMBER = :CUSTOMERNUMBER
INTO :PERSONID

DO
begin
EXECUTE PROCEDURE GETPERSON(:PERSONID)
RETURNING_VALUES
( :TITLE, :FIRSTNAME, :LASTNAME, :STREET, :CITY,
:STATE , :ZIP , :PHONE , :CELL , :EMAIL );
SUSPEND;
end
end

/heLen