SQL Server operand type clash!

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.