Changing ORDER BY calls "error conversion type"

Question

I want a preface, I did not write this. I inherited this earlier today, and I struggled to find a solution. The following code base works - except that the price per square foot is returned in alphabetical order, not in numbers.

SELECT CTE.Suite, CTE.Building, CS.Address, CS.City, CS.State, CTE.Tenant, CTE.AverageCharges, CTE.SQFT, CTE.CostSQFT, CTE.TenantType FROM cteInvoices as CTE ... ORDER BY CASE @Order WHEN 'Suite' THEN CTE.Suite WHEN 'Cost' THEN cast(CTE.costSQFT as varchar) END 

I changed the end of the case to the following:

  ORDER BY CASE @Order WHEN 'Suite' THEN CTE.Suite WHEN 'Cost' THEN CTE.costSQFT END 

This fixed the sorting of costs, however, when I choose to sort by Suite, I get:

  Msg 8114, Level 16, State 5, Procedure rCostSQFTcsort, Line 17 Error converting data type varchar to numeric. 

I am working on this evening, and nothing that I seem to be correcting is if something tends to make the situation worse. I hope that a second set of eyes can shed light on the problem.

Answer 1

If they are not converted to the same type, you may need to separate them into two separate sentences. The reason is that CASE is an expression that returns the value of one data type, and the data type represented by all potential options must be convertible. If there is no ELSE , NULL returned, so it will order all the rows the same way for this "branch":

  ORDER BY CASE @Order WHEN 'Suite' THEN CTE.Suite END, CASE @Order WHEN 'Cost' THEN CTE.costSQFT END; 

Perhaps you can explicitly convert one of them, but that can change its value (for example, you probably still want 11.0 sort after 9.0 , and converting both to a string will not do this.