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
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
For testing purpose, I have added 5 lakh records into the Employee table.
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
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.
HostForLIFE.eu SQL Server 2012 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes. We have customers from around the globe, spread across every continent. We serve the hosting needs of the business and professional, government and nonprofit, entertainment and personal use market segments.
