sql-server

Filtering, Paging and Sorting in SQL Server 2008

This article provide one solution to achieve server side paging, sorting and filtering in SQL Server 2008.

Abhith Rajan
Abhith RajanNovember 22, 2018 · 2 min read · Last Updated:

Stored procedure to achieve paging, sorting and filtering in SQL Server 2008 is given below.

1CREATE PROCEDURE [dbo].[uspTableNameOperationName] @Query NVARCHAR(50) = NULL,
2 @Offset INT = 0,
3 @PageSize INT = 10,
4 @Sorting NVARCHAR(20) = 'ID DESC',
5 @TotalCount INT OUTPUT
6AS
7 BEGIN
8 SET NOCOUNT ON;
9 SET @Query = LTRIM(RTRIM(@Query));
10 SELECT @TotalCount = COUNT([Id])
11 FROM [dbo].[TableName]
12 WHERE IsDeleted = 0
13 AND (@Query IS NULL
14 OR [ColumnOne] LIKE '%' + @Query + '%'
15 OR [ColumnTwo] LIKE '%' + @Query + '%');
16 WITH CTEResults
17 AS (SELECT Id,
18 [ColumnOne],
19 [ColumnTwo],
20 [ColumnThree],
21 ROW_NUMBER() OVER(
22 ORDER BY CASE
23 WHEN(@Sorting = 'ID DESC')
24 THEN [Id]
25 END DESC,
26 CASE
27 WHEN(@Sorting = 'ID ASC')
28 THEN [Id]
29 END ASC,
30 CASE
31 WHEN(@Sorting = 'COLUMNONE ASC')
32 THEN [ColumnOne]
33 END ASC,
34 CASE
35 WHEN(@Sorting = 'COLUMNONE DESC')
36 THEN [ColumnOne]
37 END DESC,
38 CASE
39 WHEN(@Sorting = 'COLUMNTWO ASC')
40 THEN [ColumnTwo]
41 END ASC,
42 CASE
43 WHEN(@Sorting = 'COLUMNTWO DESC')
44 THEN [ColumnTwo]
45 END DESC) AS RowNum
46 FROM [dbo].[TableName]
47 WHERE IsDeleted = 0
48 AND (@Query IS NULL
49 OR [ColumnOne] LIKE '%' + @Query + '%'
50 OR [ColumnTwo] LIKE '%' + @Query + '%');
51 SELECT *
52 FROM CTEResults
53 WHERE RowNum BETWEEN @Offset + 1 AND(@Offset + @PageSize);
54 END;
55GO

In the above query, we are using CASE to do the conditional sorting. And with help of a common table expression (CTE), we do the paging. For filtering (here only string comparison), we are using LIKE.

If you are using SQL Server 2012 or later, then you have other options to do paging like by using OFFSET FETCH.

Story in short

During this post is written, I was working on an Angular 6 + ASP.NET Core 2.1 web app. The DB provided was a SQL Server 2008. Used Dapper to do the repository level coding.

Additional Resources

This page is open source. Noticed a typo? Or something unclear?
Improve this page on GitHub


Abhith Rajan

Written byAbhith Rajan
Abhith Rajan is a software engineer by day and a full-stack developer by night. He's coding for almost a decade now. He codes 🧑‍💻, write ✍️, learn 📖 and advocate 👍.
Connect

Is this page helpful?

Related SnippetsView All

Related ArticlesView All

Related VideosView All

Using SQL Data Sync for Bidirectional Data Replication in SQL Server & Azure SQL DBs | Data Exposed

Build fast, scalable data system on Azure SQL Database Hyperscale | Clearent

SQL Server 2017 Graph Database

Related Tools & ServicesView All

sqlfum.pt

sequel fumpt

sqlfmt is an online SQL formatter. It is pronounced sequel fumpt. Its purpose is to beautifully format SQL statements.
SmarterASP.NET

SmarterASP.net - Unlimited ASP.NET Web Hosting

ASP.NET Hosting by SmarterASP.net. Unlimited ASP.NET Hosting Plans Starting at $2.95 a month.