Custom Pagination with Dynamic ORDER BY

SQL Server Denali has a new feature allowing pagination using the order by clause.

A common solution needed for the front end is to paginate records prior to sending them to the webserver. More frequently now, we are seeing demormalized data sets being stored in the WebServer’s or a middle tiers cache mechanism. Those solutions however are more difficult to maintain, persist and synchronize. Enter the old fashioned database paging solution.

This paging solution initially grabs a subset of a table and counts the records. It then stores ordered results based on the parameter passed into the common table expression. Additional parameters are the number of rows the caller wants on each page and the page number the caller is currently retrieving.

[cc lang=”sql”]
CREATE PROCEDURE dbo.GetEmployees
(
@SortColumn VARCHAR(50) = null,
@iRows INT = 10,
@iPageNum INT = 1
)
AS
BEGIN

SET NOCOUNT ON

DECLARE @RecordCount int
DECLARE @iNbrPages int

SET @RecordCount = 0
SET @iNbrPages = 0

SELECT
emp.EmployeeID,
emp.FirstName,
emp.LastName,
emp.DateHired
INTO #Employees
FROM HR.Employees emp
WHERE emp.IsTerminated = 1

SELECT
@iNbrPages = CEILING(COUNT(1)/(@iRows*1.0)),
@RecordCount = COUNT(1)
FROM #Employees

BEGIN

;WITH PagingCTE
(
Row_ID,
EmployeeID,
FirstName,
LastName,
DateHired
)
AS
(
SELECT
ROW_NUMBER()
OVER(ORDER BY
CASE WHEN @SortColumn = ‘EmployeeID’ THEN emp.EmployeeID END ASC,
CASE WHEN @SortColumn = ‘FirstName’ THEN emp.FirstName END ASC,
CASE WHEN @SortColumn = ‘LastName’ THEN emp.LastName END ASC,
CASE WHEN @SortColumn = ‘DateHired’ THEN emp.DateHired END ASC
) AS [Row_ID],
emp.EmployeeID,
emp.FirstName,
emp.LastName,
emp.DateHired
FROM #Employees emp
)

SELECT
emp.EmployeeID,
emp.FirstName,
emp.LastName,
emp.DateHired,
PageNumber = @iPageNum,
TotalNbrPages = @iNbrPages,
TotalRecords = @RecordCount
FROM PagingCTE emp
WHERE Row_ID <= (@iRows * @iPageNum) - (@iRows - 1) AND Row_ID <= @iRows * @iPageNum END [/cc] A more efficient way to do this would be to pass in a sessionID unique to the user, then create a temp table based upon that users sessionID. Then on each call to the procedure, the procedure could check for the existence of the temp table so the result would not have to be queried again.