Pagination and sorting in MS SQL Server 2000
March 13th, 2008 admin
I have developed my own way of sorting and paging data without using dynamic queries. All I use is stored procedures with 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)
as you see all the parameters have default values - so if we do not pass any of them the sproc will return all the data without considering pagination or sorting.
Of course it would be much easier if MS SQL 2000 could understand the syntax:
SELECT Field1, Field2, Fieldn FROM MyTable LIMIT @FirstRecordToDisplay, @FirstRecordToDisplay + @NumberOfRecordsToDisplay ORDER BY @OrderByField (CASE WHEN @OrderByAsc = 1 THEN ASC ELSE DESC END)
but unfortunately it doesn't. So I am gonna show another way to solve this problem.
I have a table Employee, which I am going to use for the example. I need to write a procedure retrieving all the rows of the table considering pagination and sorting.
Structure of the table:
EmployeeID int IDENTITY(1, 1) PRIMARY KEY,
FromDate datetime,
FirstName varchar(50),
LastName varchar(50),
EmailAddress varchar(255)
So please see the text of the procedure below:
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 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!
files: usp_GetAllEmployees
Posted in development, sql | 2 Comments »