CODEWITHSUNDEEP
sql-server-2016
radders
radders

Reputation: 923

Error in ORDER BY clause using CASE WHEN

I'm trying to allow ASC/DESC sort order to be defined by a parameter in a stored procedure.

After lots of research, I found this approach (with simplification):

SELECT *
FROM MyTable
ORDER BY CASE WHEN @reverse = 1 THEN
              MyColumn
         END DESC,
         CASE WHEN @reverse = 0 THEN
              MyColumn
         END ASC

However, this code throws the following error:

Msg 408, Level 16, State 1, Line 8
A constant expression was encountered in the ORDER BY list, position 2.

Why is this happening? Clearly MyColumn isn't a constant - it is a column name.

Using SQL Server 2016 in Compatibility Mode 2016 (130)

Thanks

Upvotes: 0

Views: 1311

Answers (2)

radders
radders

Reputation: 923

The real underlying cause of all this grief is the fact that:

ORDER BY MyColumn ASC

requires the

ASC

to be a hard-coded string (like SELECT, FROM, etc.) and can't be a string variable. ;-((

In order to overcome this limitation, and the problem caused by trying to use CASE to overcome it, I have made 95% of the query fill a table-variable, then I have one of two queries which SELECT from it with the correct ORDER BY clause.

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28890

After some search this line helped me understand more..

ordering by an expression must evaluate to a constant

so as Lamak pointed out,1=0 evaluates to false and you didn't define an else condition..so null is undefined and it throws error

to get rid of that try like below

ORDER BY CASE WHEN 1 = 1 THEN
              MyColumn
         END DESC,
         CASE WHEN 1 = 0 THEN
              col2 else col2--not your column,added this to make example clearer
         END ASC

also beware ,expressions in order by must be unique,so your query won't work(even if it succeeds) and throws different error,you can use ISNULL as well

Upvotes: 1

Related Questions