LinkedIn
GitHub profile
Проекты
Advertisement
RSS
.NET Framework .NET C# VB.NET LINQ ASP.NET Web API REST SignalR Windows Forms WPF WCF RabbitMQ PHP SQL Server MySQL PostgreSQL MariaDB SQLite MongoDB ADO.NET ORM Entity Framework Dapper XML JSON HTML5 CSS3 Bootstrap JavaScript jQuery Angular React TypeScript NPM Blazor UI/UX Responsive Web Design Redis Elasticsearch GraphQL Grafana Agile Scrum Kanban Windows Server IIS PowerShell Active Directory TFS Azure Automation Software Reverse Engineering Performance Optimization Git Jira/Confluence CI/CD TeamCity SOLID KISS DRY YAGNI
Always will be ready notify the world about expectations as easy as possible: job change page

Effective paging, sorting and filtering using SQL Server Stored Procedure

Created: Jan 6, 2023
Author: Pankaj Patel
Source: https://www.c-sharpcorner.com/article/effective-paging-sorting-and-filtering-using-sql-server-stored-procedure/
Views: 188

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.

Send message
Email
Your name
*Message


© 1999–2023 WebDynamics
1980–... Sergey Drozdov
Area of interests: .NET | .NET Core | C# | ASP.NET | Windows Forms | WPF | Windows Phone | HTML5 | CSS3 | jQuery | AJAX | MS SQL Server | Transact-SQL | ADO.NET | Entity Framework | IIS | OOP | OOA | OOD | WCF | WPF | MSMQ | MVC | MVP | MVVM | Design Patterns | Enterprise Architecture | Scrum | Kanban