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


This way you can write the query. We use CASE.. WHEN to work with multiple conditions.

Happy Coding!!