Author: Steven Neiland
Published:

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

What Do You Think?

Reader Comments

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

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 works...so can you post more for me/us or email me more info?

thanks
dan

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

Dan,
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 EDT

Steven,
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 EDT

Carl,
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
Kapitan
Wednesday, December 16, 2015 at 2:30:52 AM EST

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

Post a Comment

Comment Etiquette:

  • 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 or appear to be advertisments, will not be published.
  • Comments that appear to be created for the purpose of linkbuilding to commercial sites will be removed.

We are all adults here so play nice.

*
*



Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub