Pages

Monday, April 29, 2013

Working with ROW_NUMBER, sort expression and sort order without dynamic query

create table #Cricketers(
ID int identity(1,1),
Name nvarchar(255),
Comments nvarchar(255)
)
insert into #Cricketers
select 'David Warner','T20-opening batsman'
union all
select 'R Ashwin','All rounder'
union all
select 'Virender Sehwag','Test-opening batsman'
union all
select 'Rahul Dravid','Former Captain'
union all
select 'MS Dhoni','Captain, Team India'
declare @OrderByDirection NVARCHAR(200)='A'
declare @OrderBy NVARCHAR(200)='Comments'
select *,ROW_NUMBER() OVER (
ORDER BY CASE WHEN @OrderBy = 'Name'
AND @OrderByDirection = 'D'
THEN Name END DESC,
CASE WHEN @OrderBy = 'Name'
AND @OrderByDirection != 'D'
THEN Name END,
CASE WHEN @OrderBy = 'ID'
AND @OrderByDirection = 'D'
THEN ID END DESC,
CASE WHEN @OrderBy = 'ID'
AND @OrderByDirection != 'D'
THEN ID END,
CASE WHEN @OrderBy = 'Comments'
AND @OrderByDirection = 'D'
THEN Comments END DESC,
CASE WHEN @OrderBy = 'Comments'
AND @OrderByDirection != 'D'
THEN Comments END
) AS RowNumber
from #Cricketers
drop table #Cricketers

No comments:

Post a Comment