Paging and sorting in sql server

Suppose you have to implement Paging and sorting then following code snippet will help you.

​create table #Student(id int identity(1,1), name nvarchar(100), birthdate datetime)

declare @PageSize int=5

declare @MinRowNumber int=1

declare @MaxRowNumber int= @MinRowNumber + @PageSize-1

declare @SortExpression nvarchar(100)='Name' ---'birthdate' Pass here column name.

declare @SortOrder nvarchar(100)='D' --'A' pass here sort order D- for descending and A for ascending.

insert #Student

select 'Viru','1986-12-07'

union all

select 'Sachin','1960-01-31'

union all

select 'Gambhir','1950-06-23'

union all

select 'Shreekant','1983-10-04'

union all

select 'Kapil','1981-03-17'

union all

select 'Yuvi','1986-03-02'

union all

select 'Dhoni','1987-12-05'

union all

select 'Zak','1975-08-17'

union all

select 'Bhajji','2007-08-06'

union all

select 'Ashwin','2010-04-22'

union all

select 'Ojha','2012-02-26'

union all

select 'Ravindra','2012-10-04'

SELECT RowNumber, name, birthdate,id FROM

(select *,ROW_NUMBER() OVER (ORDER BY

CASE WHEN @SortExpression = 'Name' AND @SortOrder='D' THEN Name END DESC,

CASE WHEN @SortExpression = 'Name' AND @SortOrder='A' THEN Name END ASC,

CASE WHEN @SortExpression = 'birthdate' AND @SortOrder='D' THEN birthdate END DESC,

CASE WHEN @SortExpression = 'birthdate' AND @SortOrder='A' THEN birthdate END ASC

) AS RowNumber from #Student) AS Student

WHERE RowNumber BETWEEN @MinRowNumber AND @MaxRowNumber

drop table #student


Happy Coding!!