Effective Paging, Sorting And Filtering Using 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, Sorting and Filtering with Stored Procedure in MS SQL Server.
 
There are a number of articles and blogs where you can find about to do Paging, Sorting and Filtering with Stored Procedure in MS SQL Server. Hence, I started and done some research on this to find out the best solution. I found it in “Pagination with OFFSET / FETCH : A better way
 
Using OFFSET / FETCH into 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 along with 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 
  1. CREATE TABLE Employee  
  2. (  
  3.     Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY,  
  4.     Name varchar(25) NOT NULL,  
  5.     City varchar(25) NOT NULL  
  6. )  
Insert data into the table
  1. declare @i int = 1  
  2. declare @total int = 500000  
  3.   
  4. while @i <= @total  
  5. begin  
  6.     insert into Employee2 (Name, City) values (RIGHT('000000'+convert(varchar, @i),6), convert(varchar, @i%4))  
  7.     set @i += 1;  
  8. end 
For testing purpose, I have added 5 lakh records into the Employee table.
 
Create stored procedure
  1. --GetAllEmployeesWay4 '', '', 1, 25, 'Name', 'Asc'  
  2. --GetAllEmployeesWay4 'Name', '1', 1, 25, 'Name', 'Asc'  
  3. --GetAllEmployeesWay4 'City', '1', 1, 25, 'Name', 'Asc'  
  4. CREATE PROCEDURE [dbo].[GetAllEmployeesWay4]  
  5. (  
  6.     @SearchColumn NVARCHAR(50) = NULL,  
  7.     @SearchValue NVARCHAR(50) = NULL,  
  8.     @PageNo INT = 1,  
  9.     @PageSize INT = 10,  
  10.     @SortColumn NVARCHAR(20) = 'Name',  
  11.     @SortOrder NVARCHAR(20) = 'ASC'  
  12. )  
  13. AS BEGIN  
  14.     SET NOCOUNT ON;  
  15.   
  16.     SET @SearchColumn = LTRIM(RTRIM(@SearchColumn))  
  17.     SET @SearchValue = LTRIM(RTRIM(@SearchValue))  
  18.   
  19.     ; WITH CTE_Results AS   
  20.     (  
  21.         SELECT Id, Name, City from Employee  
  22.   
  23.         WHERE @SearchColumn= '' OR  (   
  24.                 CASE @SearchColumn   
  25.                     WHEN 'Name' THEN Name   
  26.                     WHEN 'City' THEN City  
  27.                 END  
  28.             ) LIKE '%' + @SearchValue + '%'  
  29.   
  30.             ORDER BY  
  31.             CASE WHEN (@SortColumn = 'Name' AND @SortOrder='ASC')  
  32.                         THEN Name  
  33.             END ASC,  
  34.             CASE WHEN (@SortColumn = 'Name' AND @SortOrder='DESC')  
  35.                         THEN Name  
  36.             END DESC,  
  37.             CASE WHEN (@SortColumn = 'City' AND @SortOrder='ASC')  
  38.                         THEN City  
  39.             END ASC,  
  40.             CASE WHEN (@SortColumn = 'City' AND @SortOrder='DESC')  
  41.                         THEN City  
  42.             END DESC   
  43.             OFFSET @PageSize * (@PageNo - 1) ROWS  
  44.             FETCH NEXT @PageSize ROWS ONLY  
  45.     ),  
  46.     CTE_TotalRows AS   
  47.     (  
  48.         select count(ID) as TotalRows from Employee  
  49.         WHERE @SearchColumn= '' OR  (   
  50.                 CASE @SearchColumn   
  51.                     WHEN 'Name' THEN Name   
  52.                     WHEN 'City' THEN City  
  53.                 END  
  54.             ) LIKE '%' + @SearchValue + '%'  
  55.     )  
  56.     Select TotalRows, t.Id, t.Name, t.City from dbo.Employee as t, CTE_TotalRows   
  57.     WHERE EXISTS (SELECT 1 FROM CTE_Results WHERE CTE_Results.ID = t.ID)  
  58.   
  59.     OPTION (RECOMPILE)  
  60. END 
Execute stored procedure
 
Execute the above stored procedure with different parameters and you can get result 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 complete example in a single folder and uploaded that with this article and it contains below script files:
  • Step1_Create_Table
  • Step2_Insert_Data_into_Table
  • Step3_Create_Stored_Procedure

Summary

 
Now, I believe you will be able to do Effective Paging, Sorting and Filtering with Stored Procedure in MS SQL Server using.
 
For more information you can go through the following articles:
  • https://sqlperformance.com/2015/01/t-sql-queries/pagination-with-offset-fetch
  • https://dotnetdaily.net/web-development/sql/fast-sql-server-stored-procedure-filtering-sorting-paging