Dynamic Paging In SQL Server Stored Procedures

Published: {ts '2011-12-06 00:00:00'}
Author: Steven Neiland
Site Url: http://www.neiland.net/article/dynamic-paging-in-sql-server-stored-procedures/

Often in web development (or any kind of application dev for that matter) you find that you need to display an ordered paged set of records to end users. Adhoc queries to the database make this very easy to do. However if you are constrained to only being able to use stored procedures then things become more complicated.

A Note On Using The Application

Now you could have your application code do the heavy lifting as far as paging (ColdFusion is great for working with databases), however even the most powerful application cannot match a database when it comes to retrieving and sorting of data. In addition even if your application could sort though large volumes of data as quickly as the database if your database is on a separate machine to your application, the higher volume network traffic to return an un-paged set of records to your app will quickly become a bottleneck.

Breaking The Problem Down

So how do we create a paged set of records using SQL stored procedures? In order to create a page set of records using a stored procedure in SQL Server we have to accomplish four things.

Work out how many total records exist based on some filter criteria.
Select all the records matching our criterea.
Order and number the records based on some sorting criteria.
Return the numbered records in a specified range (e.g. Rows 10 to 20)

Step 1: Getting Total Number Of Matching Records

In order for the application that is receiving the records to know how many pages of data are available we need to send back the total number of records that match the search criteria. This is the simplest part of the problem so let go ahead and create a simple stored proc to get user records.


CREATE PROCEDURE dbo.proc_getUsersPaged 
	@orderCol varchar(50), -- Used later for sorting
	@orderDir varchar(4), -- Used later for sorting
	@startRow int, -- Used later for paging
	@endRow int, -- Used later for paging
	@nameLike varchar(50) = null -- Used to filter
AS

BEGIN
	-- Declare variables
	DECLARE @maxRowNum INT

	-- Get the total number of records matching the filter criteria
	SELECT @maxRowNum = COUNT(*)
	FROM dbo.users
	WHERE 
	--put filtering conditions (excluding row number limits) here
	@nameLike IS NULL OR @nameLike LIKE '%'+ @nameLike + '%'
END
GO

As you can see all we are doing at this stage is defining a variable to stored the total number of records matching our criteria and storing the result of a count(*).

Step 2: Get All The Matching Records

The next step is to get all the records that match our filter criteria.


-- Get all records that match the filter criteria
SELECT
	userid
	,username
FROM dbo.users
WHERE 1=1
--put filtering conditions (excluding row number limits) here
AND (@nameLike IS NULL OR username LIKE '%'+ @nameLike + '%')

Combining this with the previous code gives us this.


CREATE PROCEDURE dbo.proc_getUsersPaged 
	@orderCol varchar(50), -- Used later for sorting
	@orderDir varchar(4), -- Used later for sorting
	@startRow int, -- Used later for paging
	@endRow int, -- Used later for paging
	@nameLike varchar(50) = null -- Used to optionally filter by username
AS

BEGIN
	-- Declare variables
	DECLARE @maxRowNum INT

	-- Get the total number of records matching the filter criteria
	SELECT @maxRowNum = COUNT(*)
	FROM dbo.users
	WHERE
	--put filtering conditions (excluding row number limits) here
	(@nameLike IS NULL OR @nameLike LIKE '%'+ @nameLike + '%')

	-- Get all records that match the filter criteria
	SELECT
		userid
		,username
	FROM dbo.users
	WHERE 
	--put filtering conditions (excluding row number limits) here
	(@nameLike IS NULL OR username LIKE '%'+ @nameLike + '%')
END
GO

Order And Number The Records

Now that we have all the records we need to number them by the sort criteria. To do this we wrap the SQL that returns the records as a source for a second SELECT. We use the ROW_NUMBER() OVER() method to then apply the row numbering as follows.


-- Apply row numbering (this is also where sorting takes place)
SELECT
	x1.*
	,ROW_NUMBER() OVER(ORDER BY userID ASC) AS rowNumber
FROM (	
	-- Get all records that match the filter criteria
	SELECT
		userid
		,username
	FROM dbo.users
	WHERE
	--put filtering conditions (excluding row number limits) here
	(@nameLike IS NULL OR username LIKE '%'+ @nameLike + '%')
) AS x1

Dynamic Sorting

You will note at this stage that the "Order By" condition is hard coded to "userid desc". In the real world though we may want to order our results by a different datapoint such as username. To do this it would be great if we could do something like this.


ORDER BY @orderCol @orderDir

Unfortunately this is not possible so we need to get creative. To date the only way I have found to implement dynamic sorting in a stored procedure is to use a rather ugly and verbose hack using a case statement.


ORDER BY
-- START UGLY HACK --
--For each column we would like sortable create two case statements (one for ASC and one for DESC)
CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'asc' THEN userId END ASC
,CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'desc' THEN userId END DESC
,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'asc' THEN username END ASC
,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'desc' THEN username END DESC
-- END UGLY HACK --

Combining this with the previous code gives us this.


-- Apply row numbering (this is also where sorting takes place)
SELECT
	x1.*
	,ROW_NUMBER() OVER(
		ORDER BY
		-- START UGLY HACK --
		--For each column we would like sortable create two case statements (one for ASC and one for DESC)
		CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'asc' THEN userId END ASC
		,CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'desc' THEN userId END DESC
		,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'asc' THEN username END ASC
		,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'desc' THEN username END DESC
		-- END UGLY HACK --
	) AS rowNumber
FROM (	
	-- Get all records that match the filter criteria
	SELECT
		userid
		,username
	FROM dbo.users
	WHERE
	--put filtering conditions (excluding row number limits) here
	(@nameLike IS NULL OR username LIKE '%'+ @nameLike + '%')
) AS x1

Getting The Desired Range Of Rows

Finally once we have numbered the rows we simply select out the ones that match our desired range. To do this we wrap the previous SQL in another SELECT and filter by the startRow and endRow ranges.


-- Return the records in the specified range
SELECT
	x2.*
	,@maxRowNumber as maxRowNumber
FROM (
	 -- Apply row numbering (this is also where sorting takes place)
	SELECT
		x1.*
		,ROW_NUMBER() OVER(
			ORDER BY
			-- START UGLY HACK --
			--For each column we would like sortable create two case statements (one for ASC and one for DESC)
			CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'asc' THEN userId END ASC
			,CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'desc' THEN userId END DESC
			,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'asc' THEN username END ASC
			,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'desc' THEN username END DESC
			-- END UGLY HACK --
		) AS rowNumber
	FROM (	
		-- Get all records that match the filter criteria
		SELECT
			userid
			,username
		FROM dbo.users
		WHERE 
		--put filtering conditions (excluding row number limits) here
		(@nameLike IS NULL OR username LIKE '%'+ @nameLike + '%')
	) AS x1
) AS x2
WHERE 
	rowNumber BETWEEN @startRow AND @endRow

You will note that we also included the value of the @maxRowNumber variable to the records we return at this stage. The first time the stored procedure is called, the default row number range will be passed to get some results to display on the first page. However to work out the appropriate paging mechanism the application will need know how many subsequent records exist.

The Final Code

Putting everything together we end up with the following code for our stored procedure.


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Steven Neiland
-- Description:	Returns a paged of user records
-- =============================================
ALTER PROCEDURE dbo.proc_getUsersPaged 
	@orderCol varchar(50),
	@orderDir varchar(4),
	@startRow int,
	@endRow int,
	@nameLike varchar(50) = null
AS

BEGIN 
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	-- Declare variables
	DECLARE @maxRowNum INT

	-- Get the total number of records matching the filter criteria
	SELECT @maxRowNum = COUNT(*)
	FROM dbo.users
	WHERE
	--put filtering conditions (excluding row number limits) here
	(@nameLike IS NULL OR @nameLike LIKE '%'+ @nameLike + '%')
	
	-- Return the records in the specified range
	SELECT
		x2.*
		,@maxRowNumber as maxRowNumber
	FROM (
		 -- Apply row numbering (this is also where sorting takes place)
		SELECT
			x1.*
			,ROW_NUMBER() OVER(
				ORDER BY
				-- START UGLY HACK --
				--For each column we would like sortable create two case statements (one for ASC and one for DESC)
				CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'asc' THEN userId END ASC
				,CASE WHEN lower(@orderCol) = 'userid' AND lower(@orderDir) = 'desc' THEN userId END DESC
				,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'asc' THEN username END ASC
				,CASE WHEN lower(@orderCol) = 'username' AND lower(@orderDir) = 'desc' THEN username END DESC
				-- END UGLY HACK --
			) AS rowNumber
		FROM (	
			-- Get all records that match the filter criteria
			SELECT
				userid
				,username
			FROM dbo.users
			WHERE
			--put filtering conditions (excluding row number limits) here
			(@nameLike IS NULL OR username LIKE '%'+ @nameLike + '%')
		) AS x1
	) AS x2
	WHERE 
		rowNumber BETWEEN @startRow AND @endRow
END
GO