Thanks Guys. My application requires that when @l is like xx.00, then I should return xx only. When the variable is like xx.yy, then I should return xx.yy. Now the same works with if. But it doesn't work with case.
If @l = 24.00, my rsult should be 24, if @l=24.35 , my result needs to be 24.35. But (@l - convert (int, @l) is behaving differently within an if statement and a case statement.
I think a column in a result set can only be of one data type. For example:
create table test1
(col1 decimal(38, 2))
insert into test1 values (24.35)
insert into test1 values (13.86)
create table test2
insert into test1 values (25)
insert into test1 values (14)
You may have a lot better luck going at this problem from the application (presentation) layer. Otherwise, the only way I can think of doing this in one select statement is to convert everything to character data. This will cause no end of problems for sorting, and the client will have to re-display the data, anyway. Not to mention the increased size of the resultsets going from the DB server to the client.
McCrowley was on the right track regarding a scalar select statement only returning one data type.
Your first method uses two different select statements, so in one case it is able to return decimal(38,2) and in the other it can return int.
Your second method uses a single select statement, which chooses to return an int value because that is the datatype returned by the first WHEN clause.
If you are trying to format your data, then you will need to cast it as a varchar anyway because assigning the result of your CASE statement to some other numeric variable just recast it as that variable type.
This, for example, works fine and is simpler than your final solution:
when @l - convert(int, @l) = 0 then cast(floor (@l) as varchar(50))
else cast(@l as varchar(50))
...because the datatype of the return value is consistent, and is able to hold values of format ## as well as ##.##
Who do blindman?
If it's not practically useful, then it's practically useless.