Returning two recordsets from a method with returntype of JSON?

coldfusion, coldfusion-10, json

Solution

A function can only return a single object. Just put the queries inside another object, like an array or structure, and return that object instead of a query. I would use a structure as they are more intuitive than arrays. Then in your JSON, you can access each resultset by key name: "orders" or "regions". I cannot test this right now, but something along these lines:

<cffunction name="OrdersandRegions" returntype="struct" returnformat="JSON">
    <cfstoredproc procedure="account_customer_EnrolmentsSEL" ...>
        <cfprocresult name = "Local.UserOrders" resultset="1">
        <cfprocresult name = "Local.UserRegions" resultset="2">
    </cfstoredproc>

    <cfset Local.result = {}>
    <cfset Local.result["orders"] = Local.UserOrders>
    <cfset Local.result["regions"] = Local.UserRegions>

    <cfreturn Local.result>
</cffunction>

(Side note, be sure to Local/var scope all function local variables.)

Problem

I have a CFC, which contains a method/function that runs one stored procedure and then outputs two results sets. Like such (shorted the code for speed of reading): ``` <cffunction name="OrdersandRegions" returntype="query" returnformat="JSON"> <cfstoredproc procedure="GetUserInfo"> <cfprocresult name = "UserOrders" resultset="1"> <cfprocresult name = "UserRegions" resultset="2"> </cfstoredproc> <!--- Currently only returning 1 resultset as JSON ---> <cfreturn UserOrders> </cffunction> ``` Firstly can I return both `UserOrders` and `UserRegions` result sets from a single method? If this was not in a CFC and was within a CFM page, then I was able to easily access both results by using `#UserOrders.OrderID#` or `#UserRegions.UserID#` for example. Because its being returned from a CFC and that too as JSON data, how do I achieve what I'm trying to achieve?

Original source