Hi,

I have a stored procedure that for some reason is only returning the
1st row of the result set to my ColdFusion 7 application. If I run the
actual query from CF7 it returns the proper number of rows in the
result set, but the stored procedure only returns the 1st one. Can
anyone see why I am not getting the entire result set. Here is the SP:

SET TERM ^ ;

CREATE PROCEDURE PROC_FINDPT (
LNAME VARCHAR(100),
FNAME VARCHAR(100))
RETURNS (
PTID INTEGER,
PLNAME VARCHAR(100),
PFNAME VARCHAR(100),
PMNAME VARCHAR(100),
PBDAY TIMESTAMP,
PADDRESS VARCHAR(200),
PCITY VARCHAR(100),
PSTATE VARCHAR(10),
PZIP VARCHAR(10))
AS
BEGIN
FOR
SELECT PATIENT.PTID, PATIENT.LASTNAME,
PATIENT.FIRSTNAME, PATIENT.MIDNAME,
CAST(PATIENT.BIRTHDATE AS TIMESTAMP) AS BIRTHDATE,
PTADDRESS.ADDRESS, PTADDRESS.CITY, PTADDRESS.STATE,
PTADDRESS.ZIP
FROM PATIENT, PTADDRESS
WHERE UPPER(PATIENT.LASTNAME) LIKE UPPER(:LNAME||'%')
AND UPPER(PATIENT.FIRSTNAME) LIKE UPPER(:FNAME||'%')
AND PATIENT.PTID=PTADDRESS.PTID
ORDER BY PATIENT.LASTNAME, PATIENT.FIRSTNAME
INTO :PTID, :PLNAME, :PFNAME, :PMNAME, :PBDAY,
:PADDRESS, :PCITY, :PSTATE, :PZIP
DO
SUSPEND;

END
^

SET TERM ; ^

Thanks for any help.

Regards,
Roy F.