Pagination and Sorting in MS SQL Server 2000

• Download usp_getallemployees - 768 B

Introduction

Sometimes while building an application (especially web-application) we need to have an opportunity to paginate through the data and to sort it. But we want the database to do all this stuff and we want to have one sproc that would return a part of data we need for the selected page and also this data should be sorted. I was trying to find related articles in the internet but most of them offered using dynamic queries. So I have built my own stored procedure.

Using the code

I have attached a file to this article contaning a sql script which demonstrates how we could create a sproc that would help to paginate and sort data.

As an example have used a table Employee, that has the following structure:

CREATE TABLE Employee
(
EmployeeID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
FromDate datetime,
FirstName varchar(50), 
LastName varchar(50), 
EmailAddress varchar(255), 
)

The procedure has the following parameters:

@OrderByField varchar(50) = NULL, -- the name of the field to sort by
@OrderByAsc bit = 1,-- direction of sorting: 1 - ASCENDING, 0 - DESCENDING
@FirstRecordToDisplay int = NULL, -- (for pagination)
@NumberOfRecordsToDisplay int = NULL --(for pagination) 

The text of the procedure:

 /*
** procedure getting records from table Employee 
*/
CREATE PROCEDURE usp_GetAllEmployees
-- parameters for pagination and sorting
@OrderByField varchar(50) = NULL,
@OrderByAsc bit = 1,
@FirstRecordToDisplay int = NULL,
@NumberOfRecordsToDisplay int = NULL
-- end of the parameters for pagination and sorting
AS
SET NOCOUNT ON
DECLARE @TotalCount int -- this variable we are gonna use later to see the total number of the records in the table

-- here we need to create a temporary table from which we will be getting a final recordset
IF OBJECT_ID('tempdb..#tmpGetEmployee') IS NOT NULL DROP TABLE #tmpGetEmployee

-- the temporary table has to have all the fields of the base table and one extra-field wich is auto-increment and a primary key
-- we are gonna use that field for sorting and paging data
CREATE TABLE #tmpGetEmployee
(
    tmpGetID int IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
    EmployeeID int,
    FromDate datetime,
    FirstName varchar(50),
    LastName varchar(50),
    EmailAddress varchar(255)
)

-- then the temporary table is being filled from the base one with sorted data
INSERT #tmpGetEmployee(EmployeeID, FromDate, FirstName, LastName, EmailAddress)
SELECT EmployeeID, EmployeeFromDate, FirstName, LastName, EmailAddress
FROM [Employee]
ORDER BY
CASE WHEN @OrderByField = 'EmployeeID' THEN EmployeeID ELSE NULL END,
CASE WHEN @OrderByField = 'FromDate' THEN FromDate ELSE NULL END,
CASE WHEN @OrderByField = 'FirstName' THEN FirstName ELSE NULL END,
CASE WHEN @OrderByField = 'LastName' THEN LastName ELSE NULL END,
CASE WHEN @OrderByField = 'EmailAddress' THEN EmailAddress ELSE NULL END
-- now the temporary table contains all the sorted data from the base table
-- but the direction of sorting has not been considered
-- that is what we gonna do-- getting total number of the records
SET @TotalCount = @@ROWCOUNT

-- coefficient of direction (asc -> @k = 1; desc - > @k = -1)
DECLARE @k smallint

-- the variable that should store tmpGetID value from the table #tmpGetEmployee -
-- which record from this table should be displayed first depending on the sorting direction
DECLARE @NewFirstRecord int
IF @OrderByAsc = 0
BEGIN
    SET @k = -1
    -- if the direction is ASC we should display the data starting from the bottom of the table
    SET @NewFirstRecord = @TotalCount - ISNULL(@FirstRecordToDisplay, 1) + 1
END
ELSE
BEGIN
    SET @k = 1
    SET @NewFirstRecord = ISNULL(@FirstRecordToDisplay, 1)
END

-- final recordset - a bit confusing, but it works!
SELECT
EmployeeID, FromDate, FirstName, LastName, EmailAddress
FROM #tmpGetEmployee
WHERE (@FirstRecordToDisplay IS NULL OR (tmpGetID - @NewFirstRecord) * @k >= 0 )
AND (@NumberOfRecordsToDisplay IS NULL OR (@NewFirstRecord + @k * @NumberOfRecordsToDisplay - tmpGetID) * @k > 0)
ORDER BY @k * tmpGetID

Good luck!