Echilon Echilon - 15 days ago 8
SQL Question

ORDER BY <int or varchar>

I have a query in a stored procedure which needs to be sorted by either an integer column or a

varchar
column, depending on one of the parameters passed to the procedure.

I have this as my
ORDER BY
clause:

...
ORDER BY
CASE @ReportType
WHEN 1 THEN
ItemName + SubItemName
WHEN 2 THEN
ItemName + SubItemName
WHEN 3 THEN
SubItemName
WHEN 4 THEN
CONVERT(int,ItemCode)
ELSE
ItemName
END


For clarity, my table structure is something like this (irrelevant columns omitted):

ItemName ItemCode SubItemName SubItemCode
Apple AA1 Fuji FJ1
Apple AA1 Gala GL1
Chicquita 1
DelMonte 2
Plantain -1
Orange OR1 Satsuma SS3
Orange OR1 Valencia VL2


If I try to run the query with @ReportType as 1,2 or 4 everything works, but if I pass a 3, I get an error:

Conversion failed when converting the varchar value 'Orange' to data type int.


I believe this is because every case in the
ORDER BY
clause has to have the same data type. I know I could convert everything to a varchar, but if I sort by
CONVERT(varchar,ItemCode)
, I get funky ordering:

Chicquita 1
Plantain -1
DelMonte 2


is it possible to sort by a varchar field in some cases, but an integer in other cases?

Answer

The problem is that CASE returns a single type. So, just use multiple keys in the order by:

ORDER BY (CASE WHEN @ReportType IN (1, 2) THEN ItemName + SubItemName END),
         (CASE WHEN @ReportType = 3 THEN SubItemName END),
         (CASE WHEN @ReportType = 4 THEN CONVERT(int, ItemCode) END),
         ItemName

The CASE expressions have no ELSE clause, so they return NULL for non-matching values.