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.

Find more about SQL Server- SQL Server.

Article Overview

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

How to do effective Paging, Sorting, and Filtering with Stored Procedure

To implement and execute this, let us follow three steps.

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.

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.

Conclusion

I believe you can do Effective Paging, Sorting, and Filtering with Stored Procedures in MS SQL Server.