Re: [CFCDev] Multiple stored proc resultsets

Nando Tue, 11 Apr 2006 07:09:37 -0700

As far as the returntype goes, it looks fine to me.

And in my experience, i'm usually better off with a more modular approach. But i suppose it depends. If in this particular case, speed is more important than flexibility, you could always keep this productDetails method and build separate methods for each individual query, prodinfo, relatedprods, and images, while you are there.

Geoff Parkhurst wrote:

I have a stored proc which returns 3 different resultsets as follows:


<cfstoredproc datasource="#application.ds#" procedure="someproc">
	<cfprocparam dbvarname="prodid" value="#val(somevalue)#"
cfsqltype="cf_sql_integer" type="in"/>
	<cfprocresult name="variables.prodinfo" resultset="1"/>
	<cfprocresult name="variables.relatedprods" resultset="2"/>
	<cfprocresult name="variables.images" resultset="3"/>
</cfstoredproc>

I would like to stick this in a CFC, but I'm not sure what my returntype
should be...

Would this be suitable:

<cffunction name="productdetails" access="public" returntype="struct">
	<cfargument name="somevalue" type="numeric" required="yes"/>
	<cfset var something=structnew()/>

	<!--- test arguments.somevalue a bit more to see if it's a positive
integer etc --->

	<cfstoredproc datasource="#variables.ds#" procedure="someproc">
		<cfprocparam dbvarname="prodid"
value="#arguments.somevalue#" cfsqltype="cf_sql_integer" type="in"/>
		<cfprocresult name="something.prodinfo" resultset="1"/>
		<cfprocresult name="something.relatedprods" resultset="2"/>
		<cfprocresult name="something.images" resultset="3"/>
	</cfstoredproc>
	<cfreturn something/>
</cffunction>

I don't really know if this stored proc is faster than 3 individual
queries... I have a nagging feeling I should really have three cffunctions,
each responsible for returning a query on their own to keep things discreet
and modular, but I'm not sure.

Any thoughts greatly appreciated.
Cheers,
Geoff




----------------------------------------------------------
You are subscribed to cfcdev. To unsubscribe, send an email to [email protected] with the words 'unsubscribe cfcdev' as the subject of the email.

CFCDev is run by CFCZone (www.cfczone.org) and supported by CFXHosting (www.cfxhosting.com).

An archive of the CFCDev list is available at www.mail-archive.com/[email protected]

Aria Media Sagl
CP 234
6934 Bioggio
Switzerland
www.aria-media.com

Re: [CFCDev] Multiple stored proc resultsets

Reply via email to