Note: This article was originally published to the Wharton Computing Developer Center.
Yesterday a fellow developer hit a strange SQL error and determined that the culprit was a T-SQL CASE statement used in his ORDER BY clause.
1 2 3 4 5 6 7 8 9 10
ORDER BY CASE WHEN UPPER(@orderBy)='GROUPMESSAGEID' THEN groupMessageID WHEN UPPER(@orderBy)='GAMEID' THEN gameID WHEN UPPER(@orderBy)='GROUPID' THEN groupID WHEN UPPER(@orderBy)='MESSAGEID' THEN messageID WHEN UPPER(@orderBy)='ROUNDSENT' THEN roundSent WHEN UPPER(@orderBy)='SENTON' THEN sentOn ELSE groupMessageID END
This code results in the following message:
Operand type clash: uniqueidentifier is incompatible with datetime
After some digging around, we found the underlying cause of the error: the case statement return values have different data types. In some cases, returning different data types will behave as expected. However, mixing numeric and character data causes problems.
In the statement above, gameId, groupID, roundSent, and groupMessageID are integers, sentOn is a datetime, and messageID is a uniqueidentifier. Because the data type precedence pecking order in this case is datetime, int, and then uniqueidentifier, SQL choose datetime as the return type. Uniqueidentifiers cannot be converted to datetimes, hence the error message.
It all became clear after reading this article by George Mastros. Thank you to George.