CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

Order by ASC or DESC based on parameter?

In the following query I use a @IsDescOrder to set the ordering.

I try to specify the order by (ie. ASC or DESC) but I get an error. I can remove the DESC | ASC but both options will return the same set because it will return the default ordering:

declare @IsDescOrder bit = 0

;with cte as 
(
 select 
 *
 from (
  select *, r = row_number() over (partition by NameId 
  order by 
      case @IsDescOrder 
        when 1 then size_mbps DESC,
        when 0 then size_mbps ASC
      end
  ) 
  from #temp_table 
  ) a 
 where r <= 20
)
select 
* from 
cte

I get error Incorrect syntax near the keyword 'DESC'..

Is there a workaround?

Upvotes: 1

Views: 4796

Answers (2)

Martin
Martin

Reputation: 16433

You have the right idea but just need to change the way your CASE is working a little:

ORDER BY
  CASE WHEN @IsDescOrder = 1 THEN DateKey ELSE '' END DESC,
  CASE WHEN @IsDescOrder = 0 THEN DateKey ELSE '' END ASC

This contains two clauses to order by, an descending order clause followed by an ascending one.

When @IsDescOrder is 1 then the descending clause is used, otherwise the ascending. By supplying ELSE '', this ensures that the unwanted order (either ASC or DESC) is effectively ignored.

EDIT

As pointed out by OP, this caused an error if the order column was numeric. To solve this, replace the empty string ('') with 0:

ORDER BY
  CASE WHEN @IsDescOrder = 1 THEN DateKey ELSE 0 END DESC,
  CASE WHEN @IsDescOrder = 0 THEN DateKey ELSE 0 END ASC

This works in the same was as the solution with the empty string, but prevents the type conversion error when using numeric columns.

Upvotes: 10

Luuk
Luuk

Reputation: 14929

DECLARE @IsDescOrder bit = 1
DECLARE @sql VARCHAR(MAX) = 'SELECT TOP 10 i FROM TEST'
SET @sql = @sql + CASE WHEN @IsDescOrder=1 THEN ' ORDER BY i DESC' ELSE ' ORDER BY i ASC' END
EXEC sp_sqlexec @sql

What you want is a 'dynamic' sql statement. MSSQL is only doing that is you use 'sp_sqlexec' (AFAIK)

Upvotes: 0

Related Questions