Return multiple resultsets using cfstoredproc

Author: Steven Neiland

Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.

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)

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

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

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

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">

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 --->
      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#">

Reader Comments

dan fredericks's Gravatar
dan fredericks
Tuesday, September 23, 2014 at 8:04:14 AM Coordinated Universal Time

Hey Steven,
can you post more of you stored proc for me? My coworker says he needs to see ins/outs of the stored proc to understand how the cfproc call can you post more for me/us or email me more info?


Steven Neiland's Gravatar
Steven Neiland
Tuesday, September 23, 2014 at 2:48:05 PM Coordinated Universal Time

I have updated the article to show the entire stored procedure. However this stored proc omits the paging arguments and code as that is outside the purpose of this demonstration.

Carl Von Stetten 's Gravatar
Carl Von Stetten
Tuesday, September 23, 2014 at 3:45:01 PM Coordinated Universal Time

One minor optimization. In your first two queries, instead of 'count(*)' maybe do 'count(username)'. Depending on the size of the table, this will allow the query to only process a single column instead of the whole table.

Steven Neiland's Gravatar
Steven Neiland
Tuesday, September 23, 2014 at 4:33:29 PM Coordinated Universal Time

I've heard of this optimization before and tested the 3 different ways of doing the count including the one you suggest.

These being
- COUNT(*)
- COUNT(1)
- COUNT(columnname)

On a sql server table with 160 million real world records each one executed exactly the same in exactly the same time.

SQL Server is very good at optimizing for these kinds of things.

I would not use the COUNT(columnname) method though because if the structure of the table were ever to change then this is one more place that would need to be updated.

Kapitan's Gravatar
Wednesday, December 16, 2015 at 2:30:52 AM Coordinated Universal Time

This article is better than the coldfusion help page that teaches the usage of cfstoredproc and cfprocresult tags. thanks for the help!

  • Please keep comments on-topic.
  • Please do not post unrelated questions or large chunks of code.
  • Please do not engage in flaming/abusive behaviour.
  • Comments that contain advertisments or appear to be created for the purpose of link building, will not be published.

Archives Blog Listing