sql-server ,

Filtering, Paging and Sorting in SQL Server 2008

by Abhith Rajan FollowNovember 22, 2018 · 2 min read · Last Updated:
Share this

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

CREATE PROCEDURE [dbo].[uspTableNameOperationName] @Query      NVARCHAR(50) = NULL,
												   @Offset     INT          = 0,
												   @PageSize   INT          = 10,
												   @Sorting    NVARCHAR(20) = 'ID DESC',
												   @TotalCount INT OUTPUT
AS
	BEGIN
		SET NOCOUNT ON;
		SET @Query = LTRIM(RTRIM(@Query));
		SELECT @TotalCount = COUNT([Id])
		FROM [dbo].[TableName]
		WHERE IsDeleted = 0
			  AND (@Query IS NULL
				   OR [ColumnOne] LIKE '%' + @Query + '%'
				   OR [ColumnTwo] LIKE '%' + @Query + '%');
		WITH CTEResults
			 AS (SELECT Id,
						[ColumnOne],
						[ColumnTwo],
						[ColumnThree],
						ROW_NUMBER() OVER(
						ORDER BY CASE
									 WHEN(@Sorting = 'ID DESC')
									 THEN [Id]
								 END DESC,
								 CASE
									 WHEN(@Sorting = 'ID ASC')
									 THEN [Id]
								 END ASC,
								 CASE
									 WHEN(@Sorting = 'COLUMNONE ASC')
									 THEN [ColumnOne]
								 END ASC,
								 CASE
									 WHEN(@Sorting = 'COLUMNONE DESC')
									 THEN [ColumnOne]
								 END DESC,
								 CASE
									 WHEN(@Sorting = 'COLUMNTWO ASC')
									 THEN [ColumnTwo]
								 END ASC,
								 CASE
									 WHEN(@Sorting = 'COLUMNTWO DESC')
									 THEN [ColumnTwo]
								 END DESC) AS RowNum
				 FROM [dbo].[TableName]
				 WHERE IsDeleted = 0
						AND (@Query IS NULL
							OR [ColumnOne] LIKE '%' + @Query + '%'
							OR [ColumnTwo] LIKE '%' + @Query + '%');
		SELECT *
		FROM CTEResults
		WHERE RowNum BETWEEN @Offset + 1 AND(@Offset + @PageSize);
	END;
GO

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 by Abhith Rajan FollowBuy me a coffee
Abhith Rajan is an aspiring software engineer with more than 6 years of experience and proven successful track record of delivering technology-based products and services.

Related Services

SmarterASP.net - Unlimited ASP.NET Web Hosting

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