Paginating Record Sets In ColdFusion With One SQL Server Call 

<!--- Kill extra output. --->
<cfsilent>
 
	<!--- Param the offset value. --->
	<cftry>
		<cfparam
			name="URL.start"
			type="numeric"
			default="1"
			/>
 
		<cfcatch>
			<cfset URL.start = 1 />
		</cfcatch>
	</cftry>
 
 
	<!--- Query for blog entries. --->
	<cfquery name="qBlog" datasource="#REQUEST.DSN.Source#">
		<!---
			Create the temporary table into which we are going
			to be storing our ID list. This list will eventually
			be joined to our target data table to limit the
			number of rows that get returned.
		--->
		DECLARE @id TABLE (
			id INT,
			row_number INT IDENTITY( 1, 1 )
		);
 
 
		<!---
			Get a variable to hold the total count of the
			matching IDs. We are going to return this as one
			of the columns.
		--->
		DECLARE @row_count INT;
 
 
		<!---
			Populate the ID temp table. Because this table has
			an AUTO INCREMENTING column, we will get not only
			the IDs that match our search criteria, but also
			the row number in which they are returned.
		--->
		INSERT INTO @id
		(
			id
		)(
			SELECT
				t.id
			FROM
				(
					<!---
						It is in this query that the MEAT of
						the query logic is performed. Here is
						where we filter and order the returned
						IDs. This filtering is just checking for
						ColdFusion, but this could be very robust
						search criteria.
 
						NOTE: I am limitting the search results
						to 1,000 records (I rarely feel the need
						to paginate more than that). However,
						you could use 100 PERCENT.
					--->
					SELECT TOP 1000
						b.id
					FROM
						blog_entry b
					WHERE
						CHARINDEX( 'ColdFusion', b.name ) > 0
					ORDER BY
						b.name ASC
				) AS t
		);
 
 
		<!---
			Get the number of total records that match our
			search criteria.
		--->
		SET @row_count = (
			SELECT
				COUNT( * )
			FROM
				@id
		);
 
 
		<!--- Get the blog entries that match the --->
		SELECT
			b.id,
			b.name,
			b.date_posted,
			b.time_posted,
 
			<!--- Get the pagination data. --->
			i.row_number,
			( @row_count ) AS row_count
		FROM
			blog_entry b
		INNER JOIN
			@id i
		ON
			(
					b.id = i.id
 
				<!--- Limit based on pagination request. --->
				AND
					i.row_number >= <cfqueryparam value="#URL.start#" cfsqltype="cf_sql_integer" />
				AND
					i.row_number < <cfqueryparam value="#(URL.start + 5)#" cfsqltype="cf_sql_integer" />
			)
		ORDER BY
			i.row_number ASC
		;
	</cfquery>
 
</cfsilent>
 
<cfoutput>
 
	<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
	<html>
	<head>
		<title>ColdFusion / SQL Pagination Demo</title>
	</head>
	<body>
 
		<h1>
			Blog Entries
		</h1>
 
		<!---
			Check to see if there were any blog entry records
			returned in our search.
		--->
		<cfif qBlog.RecordCount>
 
			<cfloop query="qBlog">
 
				<p>
					<strong>#qBlog.name#</strong><br />
					<em>
						Record #qBlog.row_number# of
						#qBlog.row_count#
					</em>
				</p>
 
			</cfloop>
 
 
			<!---
				Do pagination. Since we are only doing this WHEN
				we have returned records, we know that row_count
				will be a valid number.
			--->
			<cfloop
				index="intPage"
				from="1"
				to="#Ceiling( qBlog.row_count / 5 )#">
 
				<!---
					Calculate the start value based on the
					current page.
				--->
				<cfset intStart = (1 + ((intPage - 1) * 5)) />
 
				<!--- Output paginating link. --->
				<a
					href="#CGI.script_name#?start=#intStart#"
					>#intPage#</a>
 
			</cfloop>
 
		<cfelse>
 
			<p>
				<em>No entries were found</em>.
			</p>
 
		</cfif>
 
	</body>
	</html>
 
</cfoutput>

For Cut-and-Paste