Implementing Custom Paging, Sorting and Dynamic query in sql

This post explains and demonstrates how to implement following things

1. Custom Paging: Returning a set of records for the given page number and page size
   
2. Dynamic query: Querying based on the where string passed thru parameter
   
3. Dynamic sorting: Sorting based on the sort expression passed thru parameter
   
4. SP_ExecuteSql: Executing sql statements included within parameters
   
5. Common Table Expression: Similar to table variable
6. function row_number(): returns the current row number in a result set

The following code is to create the stored procedure GetCustomersByPage. This stored procedure accepts the parameters page size, page number to return the page, where string (It must start with 'and' keyword if a condition is added. Ex: " and CompanyName like '%soft%' " or empty is accepted but applies no filter), sort expression to get the sorted records and total count which is useful when implementing the paging logic in user interface.

The sample sproc is written for NorthWind database. If you have NorthWind database installed then you can run this sproc and see how it works.

create procedure GetCustomersByPage
@PageSize int,
@PageNumber int,
@WhereString varchar(max),
@SortExpression varchar(200),
@TotalCount int output
as
begin

declare @FirstRecord int
declare @LastRecord int


set @FirstRecord = (((@PageNumber - 1) * @PageSize) + 1)
set @LastRecord = (@PageNumber * @PageSize)

declare @sql nvarchar(max)
DECLARE @ParmDefinition NVARCHAR(500)
SET @ParmDefinition=N'@TotalCount int OUTPUT'

set @sql = 'select @TotalCount = count(*) from dbo.Customers where 1=1 '+@WhereString+';
with PagedCustomers as (select row_number() over(order by '+@SortExpression+') as RowNumber, * from dbo.Customers where 1=1 '+@WhereString+')
SELECT * from PagedCustomers where RowNumber between '+cast(@FirstRecord as varchar)+'and '+cast(@LastRecord as varchar)

exec sp_executesql @sql, @ParmDefinition, @TotalCount=@TotalCount output

return @TotalCount
end