Click here to Skip to main content
15,504,286 members
Articles / Database Development / SQL Server
Tip/Trick
Posted 18 Nov 2010

Tagged as

Stats

15.5K views
2 bookmarked

CASE/WHEN return type

Rate me:
Please Sign up or sign in to vote.
4.33/5 (3 votes)
7 Dec 2010CPOL
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior)
India India

Microsoft C# MVP (12-13)



Hey, I am Pranay Rana, working as a Team Leadin MNC. Web development in Asp.Net with C# and MS sql server are the experience tools that I have had for the past 5.5 years now.

For me def. of programming is : Programming is something that you do once and that get used by multiple for many years

You can visit my blog


StackOverFlow - http://stackoverflow.com/users/314488/pranay
My CV :- http://careers.stackoverflow.com/pranayamr

Awards:



Comments and Discussions

 
GeneralLike others have mentioned, the first solution may cause bug... Pin
AspDotNetDev13-Dec-10 8:32
protectorAspDotNetDev13-Dec-10 8:32 
GeneralI think the second solution should be the best one....... :-... Pin
Vivek Johari5-Dec-10 22:52
Vivek Johari5-Dec-10 22:52 
GeneralShouldn't you be specifying the column name for the @sortby ... Pin
Dennis.D.Allen29-Nov-10 5:04
Dennis.D.Allen29-Nov-10 5:04 
GeneralYou should fix your PRE blocks. Pin
AspDotNetDev19-Nov-10 12:25
protectorAspDotNetDev19-Nov-10 12:25 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.