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 OUTPUT6AS7 BEGIN8 SET NOCOUNT ON;9 SET @Query = LTRIM(RTRIM(@Query));10 SELECT @TotalCount = COUNT([Id])11 FROM [dbo].[TableName]12 WHERE IsDeleted = 013 AND (@Query IS NULL14 OR [ColumnOne] LIKE '%' + @Query + '%'15 OR [ColumnTwo] LIKE '%' + @Query + '%');16 WITH CTEResults17 AS (SELECT Id,18 [ColumnOne],19 [ColumnTwo],20 [ColumnThree],21 ROW_NUMBER() OVER(22 ORDER BY CASE23 WHEN(@Sorting = 'ID DESC')24 THEN [Id]25 END DESC,26 CASE27 WHEN(@Sorting = 'ID ASC')28 THEN [Id]29 END ASC,30 CASE31 WHEN(@Sorting = 'COLUMNONE ASC')32 THEN [ColumnOne]33 END ASC,34 CASE35 WHEN(@Sorting = 'COLUMNONE DESC')36 THEN [ColumnOne]37 END DESC,38 CASE39 WHEN(@Sorting = 'COLUMNTWO ASC')40 THEN [ColumnTwo]41 END ASC,42 CASE43 WHEN(@Sorting = 'COLUMNTWO DESC')44 THEN [ColumnTwo]45 END DESC) AS RowNum46 FROM [dbo].[TableName]47 WHERE IsDeleted = 048 AND (@Query IS NULL49 OR [ColumnOne] LIKE '%' + @Query + '%'50 OR [ColumnTwo] LIKE '%' + @Query + '%');51 SELECT *52 FROM CTEResults53 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.
