SQL multiple case statement

Question

How can I make this correct? I am getting an error saying: Incorrect syntax near the keyword 'DESC'.

SELECT *
FROM Companies
Order By 
    CASE @OrderByField
        WHEN 'CompanyName' THEN CompanyName
        WHEN 'CreatedDate' THEN CreatedDate
    END, 
    CASE @Direction
        WHEN 'DESC' THEN DESC
        WHEN 'ASC' THEN ASC
    END

Can I not have two case statements? If not, how can i pass in the name of the order by field and direction as parameters?

Thanks!

Another problem surface after the first one is solved...

If I include a field that doesn't have the datatype of string, if throws an error. For example:

SELECT *
FROM Companies
Order By
  CASE @Direction WHEN 'DESC' THEN
    CASE @OrderByField
        WHEN 'CompanyName' THEN CompanyName
        WHEN 'CreatedDate' THEN CreatedDate
        WHEN 'Score' THEN Score

    END
  END DESC,
  CASE @Direction WHEN 'ASC' THEN
    CASE @OrderByField
        WHEN 'CompanyName' THEN CompanyName
        WHEN 'CreatedDate' THEN CreatedDate
        WHEN 'Score' THEN Score
    END
  END ASC

@OrderByField is type of nvarchar(50) assume Score has a datatype of float.

Above throws an error like the one below even if i am not trying to order by the score field. Error converting data type nvarchar to float.

Similarly, including a createddate throws an error: Conversion failed when converting date and/or time from character string.

Will be very appreciated if anyone can help out.

i will be appreciated if you can find a post on this as well! — CYC0616
– CYC0616, Commented Jun 23, 2014 at 0:09
See answer by LarsW on the first link, or case part of answer by gbn on second link; those seem to be exactly what you're after? — JohnLBevan
– JohnLBevan, Commented Jun 23, 2014 at 0:30

Bohemian · Accepted Answer · 2014-06-22 23:31:44Z

2

You can't return a keyword from a case statement.

But you can achieve what you want by ordering in two ways, but return a constant expression for the order you don't want:

SELECT *
FROM Companies
Order By
  CASE @Direction WHEN 'DESC' THEN
    CASE @OrderByField
        WHEN 'CompanyName' THEN CompanyName
        WHEN 'CreatedDate' THEN CreatedDate
    END
  END DESC,
  CASE @Direction WHEN 'ASC' THEN
    CASE @OrderByField
        WHEN 'CompanyName' THEN CompanyName
        WHEN 'CreatedDate' THEN CreatedDate
    END
  END ASC

Non-matching cases will return null and so will be ignored for ordering purposes.

answered Jun 22, 2014 at 23:31

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

CYC0616 Over a year ago

Very elegant! this is exactly what i want. Two nested case statements was my approach after posting my question. You got there first!

CYC0616 Over a year ago

Will be appreciated if you can help out the above problem. Thanks Bohemian!

Bohemian Over a year ago

You need to cast CreatedDate to a character type, but the syntax varies across databases: What database are you using?

CYC0616 Over a year ago

i am using sql server 2013

Guffa · Accepted Answer · 2014-06-22 23:30:51Z

1

As ASC and DESC are keywords, you can't have them as return value from a CASE.

You can make two cases, one for ASC and one for DESC:

SELECT *
FROM Companies
Order By 
  CASE
    WHEN @OrderByField = 'CompanyName' AND @Direction = 'ASC' THEN CompanyName
    WHEN @OrderByField = 'CreatedDate' AND @Direction = 'ASC' THEN CreatedDate
  END ASC,
  CASE
    WHEN @OrderByField = 'CompanyName' AND @Direction = 'DESC' THEN CompanyName
    WHEN @OrderByField = 'CreatedDate' AND @Direction = 'DESC' THEN CreatedDate
  END DESC

Note that all values from a case has to have the same data type, so you might need one pair of cases for string and one pair for dates.

answered Jun 22, 2014 at 23:30

Guffa

703k111 gold badges760 silver badges1k bronze badges

2 Comments

CYC0616 Over a year ago

My apology for not seeing your comment below. thanks for forecasting the problem that i would run into.

CYC0616 Over a year ago

Question: Will your sql statement not result in something like this: "SELECT * FROM Companies Order By CompanyName ASC, DESC" ? since the second desc is outside of the case statement and will get ran no matter what.

Collectives™ on Stack Overflow

SQL multiple case statement

2 Answers 2

4 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related