Datatype conversion issue in CASE expression

Recently read a psot in a forum asking why he get strange data type conversion issue when he use CASE expression
in a order by clause. It’s pretty common misunderstanding of CASE expression. Let’s try the following similar
query in AdventureWorks database.
 
USE AdventureWorks
GO
DECLARE @SortCol nvarchar(50)
SET @SortCol=N’Name’
SELECT * FROM HumanResources.Department
ORDER BY CASE WHEN @SortCol=N’Name’ THEN [Name]
 WHEN @SortCol=N’GroupName’ THEN GroupName
 WHEN @SortCol=N’ModifiedDate’ THEN ModifiedDate
 ELSE DepartmentID
 END
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting datetime from character string.
 
This is becuase CASE Returns the highest precedence data type from the set of types in each WHEN and ELSE expression.  The data type precedence list:
user-defined data types (highest)
sql_variant
xml
datetime
smalldatetime
float
real
decimal
money
smallmoney
bigint
int
smallint
tinyint
bit
ntext
text
image
timestamp
uniqueidentifier
nvarchar
nchar
varchar
char
varbinary
binary
We have ModifiedDate as datetime, DepartmentID as int, and others as nvarchar. So SQL Server is trying to convert the result of the CASE expression to datetime.
Since the value of the string column [Name] is not compatible with datetime, the conversion fails and we get the up error.
Two ways to workaround this. One is to convert the data explicitly, e.g.:
 
DECLARE @SortCol nvarchar(50)
SET @SortCol=N’Name’
SELECT * FROM HumanResources.Department
ORDER BY CASE WHEN @SortCol=N’Name’ THEN [Name]
 WHEN @SortCol=N’GroupName’ THEN GroupName
 WHEN @SortCol=N’ModifiedDate’ THEN CONVERT(nvarchar(100),ModifiedDate ,121)
 ELSE CAST(DepartmentID as nvarchar(30))
 END
GO
However, when converted to string, if sorted by the interger column (DepartmentID), the value ‘2’ will be after
’10’. To solve this issue, we need to padd space before the numbers:

DECLARE @SortCol nvarchar(50)
SET @SortCol=N’DepartmentID’
SELECT * FROM HumanResources.Department
ORDER BY CASE WHEN @SortCol=N’Name’ THEN [Name]
 WHEN @SortCol=N’GroupName’ THEN GroupName
 WHEN @SortCol=N’ModifiedDate’ THEN CONVERT(nvarchar(100),ModifiedDate ,121)
 ELSE REPLICATE(N’ ‘,30-LEN(CAST(DepartmentID as nvarchar(30))))+CAST(DepartmentID as nvarchar(30))
 END
Another way is to use danamic SQL:
DECLARE @SortCol nvarchar(50)
DECLARE @SQL nvarchar(1000)
SET @SortCol=N’Name’
SET @SQL=N’
SELECT * FROM HumanResources.Department
ORDER BY ‘+CASE WHEN @SortCol=N’Name’ THEN N'[Name]’
 WHEN @SortCol=N’GroupName’ THEN N’GroupName’
 WHEN @SortCol=N’ModifiedDate’ THEN N’ModifiedDate’
 ELSE N’DepartmentID’
 END
EXEC (@SQL)
Advertisements
This entry was posted in SQL server. 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