CODEWITHSUNDEEP
stored-procedurescoldfusioncoldfusion-10coldfusion-11
volume one
volume one

Reputation: 7563

How to return multiple results from a stored procedure in a CFC?

I've seen an identical question here returning multiple stored procedure result sets from a cfc but I'm not able find a good solution.

I have an SP which returns about 8 results/queries. I want to output the queries into variables that can be returned by other functions. This is what I mean:

<cffunction name="AllOrders" returntype="query">
  <cfstoredproc procedure="SELAllOrders" datasource="mydb">
   <cfprocresult name="rsClosedOrders" resultset="1">
   <cfprocresult name="rsOpenOrders" resultset="2">
   <cfprocresult name="rsPendingOrders" resultset="3">
   <cfprocresult name="rsPaidOrders" resultset="4">
  </cfstoredproc>
</cffunction>
<!--- GET A RESULT TO RETURN TO CALLING PAGE --->
<cffunction name="GetClosedOrders" returntype="query">
   <!--- How do I return the result/query from the above CFC named rsClosedOrders from here? ---!>
<cfreturn rsClosedOrders/>
</cfunction>

How could I get the rsClosedOrders result out of the AllOrders() function and made available to another method, in this case GetClosedOrders()?

Upvotes: 0

Views: 2149

Answers (2)

Brad Wood
Brad Wood

Reputation: 3953

This is essentially what Leigh was describing above and with proper var scoping applied. Note, this is what you're asking for, but it's not a very efficient method. I would break the stored proc into separate procs, or just eliminate it and put the queries directly in your code.

<cffunction name="AllOrders" returntype="struct">
  <cfset local.resultSets = {}>
  <cfstoredproc procedure="SELAllOrders" datasource="mydb">
   <cfprocresult name="local.resultSets.rsClosedOrders" resultset="1">
   <cfprocresult name="local.resultSets.rsOpenOrders" resultset="2">
   <cfprocresult name="local.resultSets.rsPendingOrders" resultset="3">
   <cfprocresult name="local.resultSets.rsPaidOrders" resultset="4">
  </cfstoredproc>
  <cfreturn local.resultSets>
</cffunction>

<cffunction name="GetClosedOrders" returntype="query">
  <cfset local.resultSets = AllOrders()>
  <cfreturn local.resultSets.rsClosedOrders/>
</cfunction>

<cffunction name="GetOpenOrders" returntype="query">
  <cfset local.resultSets = AllOrders()>
  <cfreturn local.resultSets.rsOpenOrders/>
</cfunction>

<cffunction name="GetPendingOrders" returntype="query">
  <cfset local.resultSets = AllOrders()>
  <cfreturn local.resultSets.rsPendingOrders/>
</cfunction>

<cffunction name="GetPaidOrders" returntype="query">
  <cfset local.resultSets = AllOrders()>
  <cfreturn local.resultSets.rsPaidOrders/>
</cfunction>

Upvotes: 3

David Byers
David Byers

Reputation: 396

There's a couple of ways you can handle this.

  1. You could refactor the stored procedure so that it's not returning 8 different result sets then just call the stored procedure you needed.

  2. You could call the SP that returns all the 8 sets and just use the one you need, like so:

    <cffunction name="getClosedOrders" returntype="query">
    <cfstoredproc procedure="SELAllOrders" datasource="mydb">
        <cfprocresult name="rsClosedOrders" resultset="1">
    </cfstoredproc>

    <cfreturn rsClosedOrders>
</cffunction>

Basically creating a function that only returns one result set out of the eight. This is horribly inefficient though.

Upvotes: 1

Related Questions