Dynamic ORDER BY with CASE statement in SQL Server - data type issues

In Microsoft SQL Server 2005 Transact-SQL (and prior versions), you can make a dynamic ORDER BY based on a variable like so:

You can 

SET @OrderBy = 'CustomerID'

or

SET @OrderBy = 'Rating'

, sorting will fail with SQL error similar to the following: 

Conversion failed when converting character string to smalldatetime data
type.

in the first case and 

Msg 8115, Level 16, State 2, Line 3 Arithmetic overflow error converting expression to data type smalldatetime.

in the second if the optimizer encounters data that can't be converted to integer. The explanation is found in the SQL Server books online:

[The CASE statement] returns the highest precedence type from the set of types
in _result_expressions_ and the optional _else_result_expression.

This means that you cannot mix types in your CASE statement which cannot be implicitly converted into the types with higher precedence according to the following list (also found in Books Online):

  1. user-defined data types (highest)
  2. sql_variant
  3. xml
  4. datetime
  5. smalldatetime
  6. float
  7. real
  8. decimal
  9. money
  10. smallmoney
  11. bigint
  12. int
  13. smallint
  14. tinyint
  15. bit
  16. ntext
  17. text
  18. image
  19. timestamp
  20. uniqueidentifier
  21. nvarchar
  22. nchar
  23. varchar
  24. char
  25. varbinary
  26. **binary **(lowest)

Because datetime and smalldatetime with their high precedence are internally stored as 4-byte and 2-byte integers respectively, you can almost always mix integers with datetime or smalldatetime. Floating numbers don't mix unless they can be cleanly converted to integers. As a workaround you could use table variables or Common Table Expressions (CTE, new in SQL Server 2005), insert the ordered data, and query these instead. The better solution with less overhead might be to group the CASE statements according to data types. The above example would look like this:

Posted: 14 November 2006

comments powered by Disqus