This article shows how to use a SQL query to perform searching, sorting and paging by one query.
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=''
![Searching-Sorting-and-Paging-using-SQLQuery-1.jpg]()
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:
![Searching-Sorting-and-Paging-using-SQLQuery-2.jpg]()
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:
![Searching-Sorting-and-Paging-using-SQLQuery-3.jpg]()
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:
![Searching-Sorting-and-Paging-using-SQLQuery-4.jpg]()
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:
![Searching-Sorting-and-Paging-using-SQLQuery-5.jpg]()
The usages of doing searching, sorting and paging in a database level is to improve your application.