Sorting and paging in sql server 2008

In the following example I am performing searching, sorting and paging against an Employee table.

I will be searching and sorting against EmployeeId, First Name, Last Name and Title and will be paging for 5 in each page.

Create one Stored Procedure (named usp_GetAllEmployees) by defining the following input parameters.

@EmployeeID int, -- Search option Employee Id

@FirstName varchar(40),-- Search option Employee First Name

@LastName varchar(50), -- Search option Employee Last Name

@Title varchar(100), -- Search option Employee Title

@FirstRow int, -- Starting Row Number

@LastRow int, -- Ending Row Number

@SortColumn varchar(30),-- Sort Column Name

@SortOrder varchar(4) -- Sort Direction (ASC/DESC)

Now define the sorting condition as in the following. Sorting can always occur against one column at a time. Therefore I will use the ROW_NUMBER function to sort the rows against the particular column and get the row number for each record. The ROW NUMBER is required for Paging.

-- Combining Sort Column and Sort order

SET @OrderBy=@SortColumn+@SortOrder

SELECT ROW_NUMBER() OVER(ORDER BY

-- Sorting EmployeeID as Ascending order

CASE

WHEN @OrderBy = 'EmployeeIDASC' THEN EmployeeID

END ASC,

-- Sorting EmployeeID as Descending order

CASE

WHEN @OrderBy = 'EmployeeIDDESC' THEN EmployeeID

END DESC)

Define all output columns

Put a filter condition in the WHERE clause as in the following. The following statement will check the @FirstName parameter for null or empty or that the FirstName column value matches the parameter value. By writing like this you can have more than one filter condition from your application. It is not necessary to pass the value for every Search parameter.
 

WHERE

-- Filtering FirstName

(@FirstName IS NULL OR @FirstName = '' OR FirstName LIKE @FirstName)

Write one Sub query to fetch the filtered and sorted output for paging purposes. Until now we have fetched all the records by applying the filter condition.

Paging can be done now by using the ROW NUMBER value as in the following. The @FirstRow will contain the Start Page number and @LastRow will contain the End Page number. In this example it will be @FirstRow=1 and @LastRow=5. (Note: you can determine the Start and End page number from your application by setting the Page Size).
 

SELECT * FROM

(

)AS EmployeesData

WHERE RowNum >=@FirstRow AND RowNum<=@LastRow

-- Taking only the given range records

Refer to the following complete Stored Procedure:

CREATE PROCEDURE [dbo].[usp_GetAllEmployees]

(

@EmployeeID int, -- Search option Employee Id

@FirstName varchar(40), -- Search option Employee First Name

@LastName varchar(50), -- Search option Employee Last Name

@Title varchar(100), -- Search option Employee Title

@FirstRow int, -- Starting Row Number

@LastRow int, -- Ending Row Number

@SortColumn varchar(30),-- Sort Column Name

@SortOrder varchar(4) -- Sort Direction (ASC/DESC)

)

AS

SET NOCOUNT ON

DECLARE @OrderBy varchar(40)

-- Setting Sort Column and Sort Order null for empty value

IF @SortColumn IS NULL

SET @SortColumn=ISNULL(@SortColumn,'')

IF @SortOrder IS NULL

SET @SortOrder=ISNULL(@SortOrder,'')

-- Combining Sort Column and Sort order

SET @OrderBy=@SortColumn+@SortOrder

SELECT * FROM

(

SELECT ROW_NUMBER() OVER(ORDER BY

-- Sorting EmployeeID as Ascending order

CASE

WHEN @OrderBy = 'EmployeeIDASC' THEN EmployeeID

END ASC,

-- Sorting EmployeeID as Descending order

CASE

WHEN @OrderBy = 'EmployeeIDDESC' THEN EmployeeID

END DESC,

-- Sorting FirstName as Ascending order

CASE

WHEN @OrderBy = 'FirstNameASC' THEN FirstName

END ASC,

-- Sorting FirstName as Descending order

CASE

WHEN @OrderBy = 'FirstNameDESC' THEN FirstName

END DESC,

-- Sorting LastName as Ascending order

CASE

WHEN @OrderBy = 'LastNameASC' THEN LastName

END ASC,

-- Sorting LastName as Descending order

CASE

WHEN @OrderBy = 'LastNameDESC' THEN LastName

END DESC,

-- Sorting Title as Ascending order

CASE

WHEN @OrderBy = 'TitleASC' THEN Title

END ASC,

-- Sorting Title as Descending order

CASE

WHEN @OrderBy = 'TitleDESC' THEN Title

END DESC,

-- Default Sorting by EmployeeID

CASE

WHEN @OrderBy = '' THEN EmployeeID

END ASC

) AS RowNum,

COUNT (*) OVER () AS TotalEmployees,

EmployeeID,

FirstName,

LastName,

Title

FROM

Employees WITH(NOLOCK)

WHERE

-- Filtering EmployeeID

(@EmployeeID IS NULL OR @EmployeeID = 0 OR EmployeeID=@EmployeeID) AND

-- Filtering FirstName

(@FirstName IS NULL OR @FirstName = '' OR FirstName LIKE @FirstName) AND

-- Filtering LastName

(@LastName IS NULL OR @LastName = '' OR LastName LIKE @LastName) AND

-- Filtering Title

(@Title IS NULL OR @Title = '' OR Title LIKE @Title)

)AS EmployeesData

WHERE RowNum >=@FirstRow AND RowNum<=@LastRow -- Taking only the given range records

SET NOCOUNT OFF

  

Refer to the following example for the same.

1. Without any Search and Sorting option. The default condition is to load all records within the page range.

The following query will result in five records:

exec usp_GetAllEmployees @EmployeeID=null,@FirstName='',@LastName='',@Title='',@FirstRow=1,@LastRow=5,@SortColumn='',@SortOrder=''



You can see in the preceding result that there is a total 9 records.

2. With the Sorting option. I will be sorting against the First Name:

exec usp_GetAllEmployees @EmployeeID=null,@FirstName='',@LastName='',@Title='',@FirstRow=1,@LastRow=5,@SortColumn='FirstName',@SortOrder='ASC'

It will return 5 records with the FirstName sorted:



3. With Searching. I will be searching against LastName:

exec usp_GetAllEmployees @EmployeeID=null,@FirstName='',@LastName='Fuller',@Title='',@FirstRow=1,@LastRow=5,@SortColumn='',@SortOrder=''

It will return 1 record:



4. With Searching and Sorting. I will be sorting and searching against the First Name (containing):

exec usp_GetAllEmployees @EmployeeID=null,@FirstName='%An%',@LastName='',@Title='',@FirstRow=1,@LastRow=5,@SortColumn='FirstName',@SortOrder='ASC'

It will return 4 records:



5. With multiple searching, I will be searching against the First Name, Last Name and Ttile (containing):

exec usp_GetAllEmployees @EmployeeID=null,@FirstName='%An%',@LastName='%d%',@Title='Sales Representative',@FirstRow=1,@LastRow=5,@SortColumn='',@SortOrder=''

It will return 2 records:

The usages of doing searching, sorting and paging in a database level is to improve your application.