Searching, Sorting and Paging Using SQL Query

Introduction

This article shows how to use a SQL query to search, sort, and Paging by one query. Find more about SQL Server- SQL Server.

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

I will be searching and sorting against EmployeeId, First Name, Last Name, and Title, paging for five on 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 deal 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. 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 include the End Page number. In this example, it will be @FirstRow=1 and @LastRow=5.

Note. You can determine the Start and End page numbers from your application by setting the Page Size. 

SELECT * FROM
(
)AS EmployeesData WHERE RowNum >=@FirstRow AND RowNum<=@LastRow
-- Taking only the given range of records

Refer to the following complete Stored Procedure. Find more about Stored Procedure in SQL Server- 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 of 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 five 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 one 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 four records

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

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

It will return two records.

The usage of searching, sorting, and Paging at a database level is to improve your application.

Conclusion

In this article, we learned how to use a SQL query to perform searching, sorting, and Paging by one query with code examples in SQL Server.