CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
rakamakafo
rakamakafo

Reputation: 1154

Using a CASE statement in ORDER BY clause with different data types

I want to use a CASE statement in my ORDER BY clause as follows:

DECLARE @SortOr TINYINT
SELECT  @SortOr = 1

SELECT  *
FROM    [Table]
ORDER BY CASE WHEN @SortOr = 1 THEN col1
              ELSE col2
         END

But it throws an error:

Cannot convert varchar into tinyint.

What is the logic behind that? How can fix it?

Upvotes: 0

Views: 721

Answers (2)

DrCopyPaste
DrCopyPaste

Reputation: 4117

Convert all returned values by the CASE statement to VARCHAR and it will work.

SQL Server tries to convert all your returned values implicitly, because they are part of the same expression and SQL Server wants them really to be the same type; it tries to convert to INT, because INT has higher precedence than VARCHAR; so you should set the same type for this explicitly.

Upvotes: 0

Tanner
Tanner

Reputation: 22753

The underlying issue that @Damien_The_Unbeliever states perfectly in the comments is:

A single CASE expression has to produce values of one data type. So if you have THENs that produce values of different data types, the system uses the precedence rules to decide which ones to convert.

You can replicate your CASE statement to work around this, where each CASE returns a single value/data type. This would be better than converting all values to VARCHAR as suggested in the other answer, which should also perform better (you will have to test):

So new ORDER BY clause will look like:

ORDER BY CASE WHEN @SortOr = 1 THEN col1 
         END ,
         CASE WHEN @SortOr != 1 THEN col2
         END -- If you need DESC it goes after END

Upvotes: 4

Related Questions