At 06:50 PM 27/03/2005 +0000, you wrote:

>Hi Daniel,
>
> > How is the SP called within ColdFusion?
> > Are you using EXECUTE PROCEDURE PROC_FINDPT?
> > Or, SELECT ... FROM PROC_FINDPT?
> >
> > Usually for a selectable stored procedure, you need to use
> > SELECT ... FROM PROC_FINDPT(input params).
>
>Thanks you so much. The SELECT syntax was the key. I was trying to use
>EXECUTE and it wouldn't work, so I ended up calling it in ColdFusion
>like so:
>
><cfstoredproc procedure = "PROC_FINDPT" DATASOURCE="#request.dsn#"
>returnCode = "Yes">
><cfprocresult name="FindPt">
><cfprocparam type = "IN"
>CFSQLType = CF_SQL_VARCHAR
>value = "#form.lname#"
>dbVarName = LNAME>
><cfprocparam type = "IN"
>CFSQLType = CF_SQL_VARCHAR
>value = "#form.fname#"
>dbVarName = FNAME>
></cfstoredproc>
>
>This worked but only returned the first record of the set. I am not
>used to using a SELECT to call a stored procedure, but it does the job
>here. I'm not sure why the above method doesn't work, but I'll just go
>with SELECT if the results of the sp will be more than one record.
>Thanks again.
>
>Regards,
>Roy F.

There is simply no way to get multiple rows using EXECUTE PROCEDURE, as
Daniel pointed out. Your selectable procedure has to be called using
SELECT. The FOR SELECT...INTO...DO....SUSPEND loop is what makes the
multi-row output possible.

The SUSPEND statement in the procedure *does* literally suspend execution
of the procedure until the client fetches the current output row from the
buffer. Firebird driver interfaces understand selectable SPs: not only do
they not croak on a SELECT statement with a package of arguments but they
also recognise that the result set will be multi-row and will set up their
own loop for fetching results.

The IBObjects buffered dataset, for example, takes the statement

Select PTID, PLNAME, PFNAME,
PMNAME, PBDAY, PADDRESS,
PCITY, PSTATE, PZIP
from PROC_FINDPT ('McGillicuddy', 'Frederick')

and treat the query just as any other multi-row query, setting up a loop to
continue fetching rows into its client buffer until no more are available.

Other interfaces have their own ways of realising the multi-fetch. I don't
know CF at all, or what you are using for an ODBC driver. It's not clear
yet from your postings whether you have tried submitting the statement as
an ordinary SELECT statement, rather than trying to force CF to see it as a
stored procedure.

./hb