Поиск  
Always will be ready notify the world about expectations as easy as possible: job change page
Jan 6, 2023

Effective paging, sorting and filtering using SQL Server Stored Procedure

Автор:
Pankaj Patel
Источник:
Просмотров:
2769

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.

Похожее
Apr 6, 2013
Introduction A dynamic SQL in a stored procedure is a single Transact-SQL statement or a set of statements stored in a variable and executed using a SQL command. There may be several methods of implementing this in SQL Server. This...
Написать сообщение
Тип
Почта
Имя
*Сообщение