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.

Follow @AbhithRajanAbhith RajanNovember 22, 2018 · 2 min read · Last Updated: November 22, 2018

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.