ORDER BY and CASE Expression

/*

We can use CASE expression in ORDER BY clause. This is useful if a stored procedure has parameters from client side to specify ordering column and direction, e.g.:

*/

USE

AdventureWorks

GO

DECLARE @SortOrder nvarchar(5), @OrderBy sysname;

SELECT @SortOrder=N‘ASC’, @OrderBy=N‘ManagerID’

SELECT * FROM HumanResources.Employee

ORDER BY CASE WHEN @SortOrder=‘DESC’ THEN

CASE @OrderBy WHEN ‘ContactID’ THEN ContactID WHEN ‘EmployeeID’ THEN EmployeeID WHEN ‘ManagerID’ THEN ManagerID END

END DESC,

CASE WHEN @SortOrder=‘ASC’ THEN

CASE @OrderBy WHEN ‘ContactID’ THEN ContactID WHEN ‘EmployeeID’ THEN EmployeeID WHEN ‘ManagerID’ THEN ManagerID END

END ASC

GO

/*

A user can specify order by ContactID, EmployeeID, ManagerID and sort direction. In the execution plan, SQL Server evaluates the order by expressions. In the given value, the syntax is literally equivalent to ORDER BY NULL DESC, ManagerID ASC,

and the first order by column is ignored since no definition (NULL). I said litterally because if you write the query directly in the same syntax, it will fail:

Msg 408, Level 16, State 1, Line 1

A constant expression was encountered in the ORDER BY list, position 1.

*/

SELECT

*  FROM HumanResources.Employee

ORDER

BY NULL DESC, ManagerID ASC;

/*

Part of the execution plan:

  1. <OrderBy>
  2. <OrderByColumn Ascending="false">
  3. <ColumnReference Column="Expr1003" />
  4. </OrderByColumn>
  5. <OrderByColumn Ascending="true">
  6. <ColumnReference Column="Expr1004" />
  7. </OrderByColumn>
  8. </OrderBy>

 

  1. <DefinedValues>
  2. <DefinedValue>
  3. <ColumnReference Column="Expr1003" />
  4. <ScalarOperator ScalarString="CASE WHEN [@SortOrder]=N’DESC’ THEN CASE WHEN [@OrderBy]=N’ContactID’ THEN [AdventureWorks].[HumanResources].[Employee].[ContactID] ELSE CASE WHEN [@OrderBy]=N’EmployeeID’ THEN [AdventureWorks].[HumanResources].[Employee].[EmployeeID] ELSE CASE WHEN [@OrderBy]=N’ManagerID’ THEN [AdventureWorks].[HumanResources].[Employee].[ManagerID] ELSE NULL END END END ELSE NULL END">
  5. <IF>
  6. <Condition>
  7. <ScalarOperator>
  8. <Compare CompareOp="EQ">
  9. <ScalarOperator>
  10. <Identifier>
  11. <ColumnReference Column="@SortOrder" />
  12. </Identifier>
  13. </ScalarOperator>
  14. <ScalarOperator>
  15. <Const ConstValue="N’DESC’" />
  16. </ScalarOperator>
  17. </Compare>
  18. </ScalarOperator>
  19. </Condition>
  20. <Then>
  21. <ScalarOperator>
  22. <IF>
  23. <Condition>
  24. <ScalarOperator>
  25. <Compare CompareOp="EQ">
  26. <ScalarOperator>
  27. <Identifier>
  28. <ColumnReference Column="@OrderBy" />
  29. </Identifier>
  30. </ScalarOperator>
  31. <ScalarOperator>
  32. <Const ConstValue="N’ContactID’" />
  33. </ScalarOperator>
  34. </Compare>
  35. </ScalarOperator>
  36. </Condition>
  37. <Then>
  38. <ScalarOperator>
  39. <Identifier>
  40. <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="ContactID" />
  41. </Identifier>
  42. </ScalarOperator>
  43. </Then>
  44. <Else>
  45. <ScalarOperator>
  46. <IF>
  47. <Condition>
  48. <ScalarOperator>
  49. <Compare CompareOp="EQ">
  50. <ScalarOperator>
  51. <Identifier>
  52. <ColumnReference Column="@OrderBy" />
  53. </Identifier>
  54. </ScalarOperator>
  55. <ScalarOperator>
  56. <Const ConstValue="N’EmployeeID’" />
  57. </ScalarOperator>
  58. </Compare>
  59. </ScalarOperator>
  60. </Condition>
  61. <Then>
  62. <ScalarOperator>
  63. <Identifier>
  64. <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="EmployeeID" />
  65. </Identifier>
  66. </ScalarOperator>
  67. </Then>
  68. <Else>
  69. <ScalarOperator>
  70. <IF>
  71. <Condition>
  72. <ScalarOperator>
  73. <Compare CompareOp="EQ">
  74. <ScalarOperator>
  75. <Identifier>
  76. <ColumnReference Column="@OrderBy" />
  77. </Identifier>
  78. </ScalarOperator>
  79. <ScalarOperator>
  80. <Const ConstValue="N’ManagerID’" />
  81. </ScalarOperator>
  82. </Compare>
  83. </ScalarOperator>
  84. </Condition>
  85. <Then>
  86. <ScalarOperator>
  87. <Identifier>
  88. <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="ManagerID" />
  89. </Identifier>
  90. </ScalarOperator>
  91. </Then>
  92. <Else>
  93. <ScalarOperator>
  94. <Const ConstValue="NULL" />
  95. </ScalarOperator>
  96. </Else>
  97. </IF>
  98. </ScalarOperator>
  99. </Else>
  100. </IF>
  101. </ScalarOperator>
  102. </Else>
  103. </IF>
  104. </ScalarOperator>
  105. </Then>
  106. <Else>
  107. <ScalarOperator>
  108. <Const ConstValue="NULL" />
  109. </ScalarOperator>
  110. </Else>
  111. </IF>
  112. </ScalarOperator>
  113. </DefinedValue>
  114. <DefinedValue>
  115. <ColumnReference Column="Expr1004" />
  116. <ScalarOperator ScalarString="CASE WHEN [@SortOrder]=N’ASC’ THEN CASE WHEN [@OrderBy]=N’ContactID’ THEN [AdventureWorks].[HumanResources].[Employee].[ContactID] ELSE CASE WHEN [@OrderBy]=N’EmployeeID’ THEN [AdventureWorks].[HumanResources].[Employee].[EmployeeID] ELSE CASE WHEN [@OrderBy]=N’ManagerID’ THEN [AdventureWorks].[HumanResources].[Employee].[ManagerID] ELSE NULL END END END ELSE NULL END">
  117. <IF>
  118. <Condition>
  119. <ScalarOperator>
  120. <Compare CompareOp="EQ">
  121. <ScalarOperator>
  122. <Identifier>
  123. <ColumnReference Column="@SortOrder" />
  124. </Identifier>
  125. </ScalarOperator>
  126. <ScalarOperator>
  127. <Const ConstValue="N’ASC’" />
  128. </ScalarOperator>
  129. </Compare>
  130. </ScalarOperator>
  131. </Condition>
  132. <Then>
  133. <ScalarOperator>
  134. <IF>
  135. <Condition>
  136. <ScalarOperator>
  137. <Compare CompareOp="EQ">
  138. <ScalarOperator>
  139. <Identifier>
  140. <ColumnReference Column="@OrderBy" />
  141. </Identifier>
  142. </ScalarOperator>
  143. <ScalarOperator>
  144. <Const ConstValue="N’ContactID’" />
  145. </ScalarOperator>
  146. </Compare>
  147. </ScalarOperator>
  148. </Condition>
  149. <Then>
  150. <ScalarOperator>
  151. <Identifier>
  152. <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="ContactID" />
  153. </Identifier>
  154. </ScalarOperator>
  155. </Then>
  156. <Else>
  157. <ScalarOperator>
  158. <IF>
  159. <Condition>
  160. <ScalarOperator>
  161. <Compare CompareOp="EQ">
  162. <ScalarOperator>
  163. <Identifier>
  164. <ColumnReference Column="@OrderBy" />
  165. </Identifier>
  166. </ScalarOperator>
  167. <ScalarOperator>
  168. <Const ConstValue="N’EmployeeID’" />
  169. </ScalarOperator>
  170. </Compare>
  171. </ScalarOperator>
  172. </Condition>
  173. <Then>
  174. <ScalarOperator>
  175. <Identifier>
  176. <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="EmployeeID" />
  177. </Identifier>
  178. </ScalarOperator>
  179. </Then>
  180. <Else>
  181. <ScalarOperator>
  182. <IF>
  183. <Condition>
  184. <ScalarOperator>
  185. <Compare CompareOp="EQ">
  186. <ScalarOperator>
  187. <Identifier>
  188. <ColumnReference Column="@OrderBy" />
  189. </Identifier>
  190. </ScalarOperator>
  191. <ScalarOperator>
  192. <Const ConstValue="N’ManagerID’" />
  193. </ScalarOperator>
  194. </Compare>
  195. </ScalarOperator>
  196. </Condition>
  197. <Then>
  198. <ScalarOperator>
  199. <Identifier>
  200. <ColumnReference Database="[AdventureWorks]" Schema="[HumanResources]" Table="[Employee]" Column="ManagerID" />
  201. </Identifier>
  202. </ScalarOperator>
  203. </Then>
  204. <Else>
  205. <ScalarOperator>
  206. <Const ConstValue="NULL" />
  207. </ScalarOperator>
  208. </Else>
  209. </IF>
  210. </ScalarOperator>
  211. </Else>
  212. </IF>
  213. </ScalarOperator>
  214. </Else>
  215. </IF>
  216. </ScalarOperator>
  217. </Then>
  218. <Else>
  219. <ScalarOperator>
  220. <Const ConstValue="NULL" />
  221. </ScalarOperator>
  222. </Else>
  223. </IF>
  224. </ScalarOperator>
  225. </DefinedValue>
  226. </DefinedValues>

*/

/*

One culprit is that the data type in the order by candidate columns must be same or compatible because CASE expression returns the data type based on Data Type Precedence of all the specified columns in the expression.

E.g. the following query used columns with different data type, when order by BirthDate it succeeds:

*/

DECLARE

@SortOrder nvarchar(5),@OrderBy sysname;

SELECT

@SortOrder=N‘DESC’,@OrderBy=N‘BirthDate’

SELECT

*  FROM HumanResources.Employee

ORDER

BY CASE WHEN @SortOrder=‘DESC’ THEN

CASE @OrderBy WHEN ‘Title’ THEN Title WHEN ‘BirthDate’ THEN BirthDate WHEN ‘EmployeeID’ THEN EmployeeID END

END DESC,

CASE WHEN @SortOrder=‘ASC’ THEN

CASE @OrderBy WHEN ‘Title’ THEN Title WHEN ‘BirthDate’ THEN EmployeeID WHEN ‘EmployeeID’ THEN EmployeeID END

END ASC

GO

/*

When order by Title it is failed:

Msg 241, Level 16, State 1, Line 4

Conversion failed when converting datetime from character string.

*/

DECLARE

@SortOrder nvarchar(5),@OrderBy sysname;

SELECT

@SortOrder=N‘DESC’,@OrderBy=N‘Title’

SELECT

* FROM HumanResources.Employee

ORDER

BY CASE WHEN @SortOrder=‘DESC’ THEN

CASE @OrderBy WHEN ‘Title’ THEN Title WHEN ‘BirthDate’ THEN BirthDate WHEN ‘EmployeeID’ THEN EmployeeID END

END DESC,

CASE WHEN @SortOrder=‘ASC’ THEN

CASE @OrderBy WHEN ‘Title’ THEN Title WHEN ‘BirthDate’ THEN EmployeeID WHEN ‘EmployeeID’ THEN EmployeeID END

END ASC

/*

This is becuase the data type of the CASE expressions are Datetime because Datatime ( of Birthdate) has higher Precedence than string.

Data type Precedence:

  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 (including nvarchar(max) )
  22. nchar
  23. varchar (including varchar(max) )
  24. char
  25. varbinary (including varbinary(max) )
  26. binary (lowest)

In the first query, when BirthDate is given as the order by column, the CASE Expression can convert BirthDate to datetime implicitly because it is of datetime;

In the second query, when Title is given as the order by column, the CASE Expression cannot convert Title to datetime implicitly because it is of string;

*/

 

Advertisements
This entry was posted in SQL server 2005. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s