CASE/WHEN return type

This post is related to how the Case/When statement return value after the executing condition available.

Problem

Recently I am working on the query where I have to sort data based on the user input but each column in table has different datatype. So to meet with this type of requirement I use case when.. block of the sql server. something as below..

DECLARE @sortby Varchar(10)
Set @sortby = 'A1'
Select String1,String2,DateCol
from (
   select 'A1','B1', GetDateTime() 
   union
   select 'A2','B2', GetDateTime()
)  As d(String1,String2,DateCol)
Order by
Case
    When @sortby = 'A1' then String2
    When @sortby = 'B1' then String1
    When @sortby = 'Date' then Date1 
End

There is not syntax error when you check for the syntax error but when execute code you find there is error

ERROR:Conversion failed when converting datetime from character string.

The problem here is first two having databype Varchar and last one having datatype DateTime. So when you executing its found that one branch having datetime so its gives error even its not executing that.

Solution

Solution 1

So to avoid this problem you require to convert dateTime to the string

DECLARE @sortby Varchar(10)
Set @sortby = 'A1'
Select String1,String2,DateCol
from (
   select 'A1','B1', GetDateTime() 
   union
   select 'A2','B2', GetDateTime()
)  As d(String1,String2,DateCol)
Order by
Case
    When @sortby = 'A1' then String2
    When @sortby = 'B1' then String1
    When @sortby = 'Date' then Cast(Date1  as varchar(20))
End

Solution 2

Divide the Case when in multiple statement

DECLARE @sortby Varchar(10)
Set @sortby = 'A1'
Select String1,String2,DateCol
from (
   select 'A1','B1', GetDateTime() 
   union
   select 'A2','B2', GetDateTime()
)  As d(String1,String2,DateCol)
Order by
Case When @sortby = 'A1' then String2 End,
Case When @sortby = 'B1' then String1 End,
Case When @sortby = 'Date' then Date1  End