So last week for the first time I had a need to return multiple datasets back to ColdFusion for a single user request. Specifically I wanted to returned a paged dataset plus count information for the entire filtered and unfiltered dataset to be used by datatables. These datasets being:

• A count of all records (visible to that user) before any filtering is applied.
• A count of all records after filtering is applied.
• The paged query resultset.

Previously I would have tackled this in one of two ways:

1. Make multiple requests to the database to get the different datasets.
2. Combine the counts into the paged query as two additional columns.

While either of these solutions would work, they just felt a little clumsy and had some code smell. I figured that there had to be a better way to tackle this.

A better way. A stored procedure returning multiple resultsets

The solution turned out to be quite elegant. Unlike cfquery, cfstoredproc allows for multiple resultsets to be returned. So bundling the three queries into a stored procedure it is possible to make just one call to the database and have all the data returned.

Example: Get a page of users from the database

So I have this simple stored proc on my database which returns the three result sets I need. Don't pay any attention to the contents of this, its just to give you an idea of what is coming back.

CREATE PROCEDURE [dbo].[proc_getUsers_paged]
      @username varchar(20)
AS
BEGIN
      SET NOCOUNT ON

      -- Count the records before filtering
      SELECT 
            COUNT(*) AS totalUnFiltered 
      FROM 
            users

      -- Count the records after filtering
      SELECT 
            COUNT(*) AS totalFiltered 
      FROM 
            users 
      WHERE 
            username LIKE '%' + @username + '%'

      -- Get the first page of data (omitting proper pagination, just get the first 10)
      SELECT TOP(10) 
            userId
            , username 
      FROM 
            users
      WHERE 
            username LIKE '%' + @username + '%'
END

The code to call this stored proc and get back the data is as follows.

<cfstoredproc datasource="#application.dsn_spoon#" procedure="proc_getUsers_paged">
      <!--- The name of a user to try match --->
      <cfprocparam cfsqltype="cf_sql_varchar" value="#username#">

      <!--- resultset containing a count of all users in the database --->
      <cfprocresult name="qResult1" resultset="1">

      <!--- resultset containing a count of all these users named steven --->
      <cfprocresult name="qResult2" resultset="2">

      <!--- resultset containing the page of users --->
      <cfprocresult name="qResult3" resultset="3">
</cfstoredproc>

Note that the number in the "resultset" attribute on the cfprocresult param corresponds to the order of the sql statements in the stored procedure.

To access these resultset we now just use the resultset name of each as specified in the name attributes.

<!--- Output the results --->
<cfoutput>
      Total number of records before filtering: #qResult1.totalUnFiltered#<br/>
      Total number of records after filtering: #qResult2.totalFiltered#<br/>
      The page of data returned:
      <cfdump var="#qResult3#">
</cfoutput>