How to sort date and time in ROW_NUMBER () OVER (ORDER BY clause

Question

Hi, the following query is a prototype of a more complex query. The problem is that I should be able to sort any column in any order (i.e. ASC and DESC) based on user input.

CREATE table #Table1( Name varchar(10) PRIMARY key, DOB DateTime, Rate numeric(10,2) ) INSERT INTO #Table1 (Name,DOB,Rate) values ('Name1','2/2/2012',10.23) INSERT INTO #Table1 (Name,DOB,Rate) values ('Name2','3/2/2012',120.23) INSERT INTO #Table1 (Name,DOB,Rate) values ('Name3','4/2/2012',110.23) INSERT INTO #Table1 (Name,DOB,Rate) values ('Name4','5/2/2012',140.23) INSERT INTO #Table1 (Name,DOB,Rate) values ('Name15','6/2/2012',130.23) INSERT INTO #Table1 (Name,DOB,Rate) values ('Name6','2/21/2012',1120.23) Declare @SortColumn varchar(10) DECLARE @SortExpression varchar (10) SET @SortColumn = 'DOB' SET @SortExpression = 'DESC' -- Need to sort in both ASC and DESC Select Name, DOB, Rate, ROW_NUMBER() OVER (ORDER BY CASE WHEN @SortColumn = 'Name' then Name WHEN @SortColumn = 'DOB' THEN DOB WHEN @SortColumn = 'Rate' THEN Rate END + @SortExpression ) AS RowNumber FROM #Table1 

Answer 1

It looks like you need CAST() and CONVERT() elements in CASE to make it work:

 Declare @SortColumn varchar(10) DECLARE @SortExpression varchar (10) SET @SortColumn = 'DOB' SET @SortExpression = 'DESC' -- Need to sort in both ASC and DESC Select Name, DOB, Rate, ROW_NUMBER() OVER (ORDER BY CASE WHEN @SortColumn = 'Name' then Name WHEN @SortColumn = 'DOB' THEN convert(char(10), DOB, 120) WHEN @SortColumn = 'Rate' THEN Cast(Rate as varchar(10)) END ASC ) AS RowNumberASC, ROW_NUMBER() OVER (ORDER BY CASE WHEN @SortColumn = 'Name' then Name WHEN @SortColumn = 'DOB' THEN convert(char(10), DOB, 120) WHEN @SortColumn = 'Rate' THEN Cast(Rate as varchar(10)) END DESC ) AS RowNumberDESC FROM Table1 

See SQL Fiddle with Demo

As @Martin, ASC , DESC noted cannot be parameterized.

Answer 2

If you need to dynamically switch between ASC and DESC , you will need to use a dynamic operator.
SQL feed here . Remember SQL Injection !

 EXECUTE(' SELECT Name, DOB, Rate,' + ' ROW_NUMBER() OVER( ORDER BY ' + @SortColumn + ' ' + @SortExpression + ' ) AS RowNumber' + ' FROM Table1'); 

If your query is complex, consider putting it in a view and choosing from that view to allow you to check the compilation time of this part.