Return multiple resultsets using cfstoredproc

Published: {ts '2014-09-23 00:00:00'}
Author: Steven Neiland
Site Url: http://www.neiland.net/article/return-multiple-resultsets-using-cfstoredproc/

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:

Make multiple requests to the database to get the different datasets.
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.

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.




	Total number of records before filtering: #qResult1.totalUnFiltered#

	Total number of records after filtering: #qResult2.totalFiltered#

	The page of data returned: