Introduction
This article will teach us about effective paging, sorting, and filtering using SQL Server Stored Procedure. Find more about Stored Procedure in SQL Server - Stored Procedure.
Article Overview
- Background.
- Prerequisites.
- How to do effective Paging, Sorting, and Filtering with Stored Procedure.
- Complete example.
- Summary.
Background
There was a situation where I had to implement fast/effective paging, orting, and Filtering with Stored Procedures in MS SQL Server.
There are several articles and blogs where you can find about to do Paging, Sorting, and Filtering with Stored Procedures in MS SQL Server. Hence, I started and did some research on this to find out the best solution. I found it in “Pagination with OFFSET / FETCH: A better way.”
Using OFFSET / FETCH in the CTE, I have created a stored procedure that was at least faster twice in return time as the alternatives found on the internet.
Here, I have kept all the implementation details and a complete example.
Prerequisites
- You should have a basic knowledge of MS SQL Stored Procedure, queries, and CTE.
How to do effective Paging, Sorting, and Filtering with Stored Procedure
To implement and execute this, let us follow three steps.
- Create table.
- Insert data into the table.
- Create stored procedure.
Create table
CREATE TABLE Employee
(
Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
Name varchar(25) NOT NULL,
City varchar(25) NOT NULL
)
Insert data into the table
declare @i int = 1
declare @total int = 500000
while @i <= @total
begin
insert into Employee2 (Name, City) values (RIGHT('000000'+convert(varchar, @i),6), convert(varchar, @i%4))
set @i += 1;
end
I have added 5 lakh records to the Employee table for testing purposes.
Create stored procedure
--GetAllEmployeesWay4 '', '', 1, 25, 'Name', 'Asc'
--GetAllEmployeesWay4 'Name', '1', 1, 25, 'Name', 'Asc'
--GetAllEmployeesWay4 'City', '1', 1, 25, 'Name', 'Asc'
CREATE PROCEDURE [dbo].[GetAllEmployeesWay4]
(
@SearchColumn NVARCHAR(50) = NULL,
@SearchValue NVARCHAR(50) = NULL,
@PageNo INT = 1,
@PageSize INT = 10,
@SortColumn NVARCHAR(20) = 'Name',
@SortOrder NVARCHAR(20) = 'ASC'
)
AS BEGIN
SET NOCOUNT ON;
SET @SearchColumn = LTRIM(RTRIM(@SearchColumn))
SET @SearchValue = LTRIM(RTRIM(@SearchValue))
; WITH CTE_Results AS
(
SELECT Id, Name, City from Employee
WHERE @SearchColumn= '' OR (
CASE @SearchColumn
WHEN 'Name' THEN Name
WHEN 'City' THEN City
END
) LIKE '%' + @SearchValue + '%'
ORDER BY
CASE WHEN (@SortColumn = 'Name' AND @SortOrder='ASC')
THEN Name
END ASC,
CASE WHEN (@SortColumn = 'Name' AND @SortOrder='DESC')
THEN Name
END DESC,
CASE WHEN (@SortColumn = 'City' AND @SortOrder='ASC')
THEN City
END ASC,
CASE WHEN (@SortColumn = 'City' AND @SortOrder='DESC')
THEN City
END DESC
OFFSET @PageSize * (@PageNo - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY
),
CTE_TotalRows AS
(
select count(ID) as TotalRows from Employee
WHERE @SearchColumn= '' OR (
CASE @SearchColumn
WHEN 'Name' THEN Name
WHEN 'City' THEN City
END
) LIKE '%' + @SearchValue + '%'
)
Select TotalRows, t.Id, t.Name, t.City from dbo.Employee as t, CTE_TotalRows
WHERE EXISTS (SELECT 1 FROM CTE_Results WHERE CTE_Results.ID = t.ID)
OPTION (RECOMPILE)
END
Execute Stored Procedure in SQL Server
Execute the above-stored procedure with different parameters, and you can get results accordingly.
- GetAllEmployeesWay4 '', '', 1, 25, 'Name', 'Asc'
- GetAllEmployeesWay4 'Name', '1', 1, 25, 'Name', 'Asc'
- GetAllEmployeesWay4 'City', '1', 1, 25, 'Name', 'Asc'
Complete example
For your reference, I have kept the complete example in a single folder and uploaded it with this article, and it contains the script files below.
- Step1_Create_Table
- Step2_Insert_Data_into_Table
- Step3_Create_Stored_Procedure
Conclusion
I believe you can do effective paging, sorting, and filtering with Stored Procedures in MS SQL Server.