Try this:
<cfquery name="qSearchProc" CACHEWITHIN="blah"
datasourse="#request.datasource#">
exec spSearchStoredProc #value1#, #value2#
</cfquery>
Signed,
Bill King
HostWorks INC
http://www.hostworks.com
-----Original Message-----
From: Darren Nickerson [mailto:[EMAIL PROTECTED]]
Sent: Thursday, May 17, 2001 10:18 AM
To: CF-Talk
Subject: Re: CFSToredProcedures
> Of course......here's an example of how to can an SP using CF.......
>
> <CFSTOREDPROC Procedure="<proc name>" DATASOURCE="<datasource>"
> RETURNCODE="Yes">
> <CFPROCPARAM TYPE="In" DBVARNAME="<dbvarname1>" CFSQLTYPE="<data type1>"
> VALUE="<value1>">
> <CFPROCPARAM TYPE="In" DBVARNAME="<dbvarname2>" CFSQLTYPE="<data type1>"
> VALUE="<value2>">
> <CFPROCRESULT name="results">
> </CFSTOREDPROC>
>
> <cfoutput query="results">.
> ...
> ...
> </cfoutput>
>
> You can also pass get the SP to pass back output params aswell as multiple
> resultsets.
We would love to be able to use ORACLE stored procedures to power a search
facility on our site, but we have been bitten by one of the MAJOR
shortcomings of CFSTOREDPROC. Whereas you can cache results sets using
CFQUERY (CACHEWITHIN attribute), we don't seem to be able to do this with
CFSTOREDPROC.
We're trying to display the results of a large query using a "pager" . . .
results 1-10 of 300, 11-20 of 300, next page, next page, etc etc. We've had
to abandon the stored procedure approach, because to display page 2 we have
to execute the stored procedure all over again and pull in the results with
a REFCURSOR. If the results come from a CFQUERY there's no problem,
displaying page 2 takes advantage of the cached results set and display is
lightning fast without ever hitting the database.
Unless I'm wrong this is something you really need to take into account when
considering using stored procedures. If you want to be able to reuse the
query results you really should use CFQUERY.
-Darren
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
