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.
About the author
This article contains a SQL query which can be used to determine the total number of open/active connections in MS SQL Server. Also explains, how to solve the error "Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached."
Using a short query we can list all the tables in the database along with the number of records in each table.
When you are in need of transforming a number to have some character to be prefixed in order to make the chars in the number a specific number long, here is one way to do it.