October 16, 2009 at 2:15 pm
Hey professionals,
I have a function that returns a value. Some greater than 0, some less than 0. I want to convert the result of this function depending on the value it returns.
So i have something like this
select Convert(int,Convert(decimal(7,2),dbo.valuetolego(lego.storename, 4)/.2))
if this select statement returns a value less than 1, then it can be converted to decimal. Otherwise it should be converted to "int". Is there a way to go about this?
My first thought was to use a case statement like so
select case when (dbo.valuetolego(lego.storename, 4)/.2) < 1
then Convert(decimal(9,2),dbo.valuetolego(lego.storename, 4)/.2)
else Convert(int,Convert(decimal(9,2),dbo.valuetolego(lego.storename, 4)/.2))
end
FROM
dbo.LEGO
this is however not returning the desired result. I know I dont have any DDL so any ideas or suggestions on how to go about his woould be gladly welcomed. Thanks.
October 16, 2009 at 2:32 pm
I dont think you are going to be able to return 2 different data types in the same column but you could round to the nearest integer for those that you want. Check out the ROUND() function in BOL
October 16, 2009 at 2:34 pm
October 16, 2009 at 3:09 pm
The business reason behind this is a long story. I also saw it as a way to explore the possibilities of sql. I thought it was impossible that was why I decided to post it up here because I have seen sql miracles here since i have been a member.
So is it impossible??
October 17, 2009 at 9:25 am
Trying to use case statements to return different datatypes will fail in some cases (like mixing character data with numeric. ) Why you would want to is beyond me because then your calling application would have to be written to anticipate different datatypes being returned.
If you want to store results with different datatypes in a column or variable, the target will have to have datatype sql_variant, or in some cases it might be varchar() or nvarchar() or any format that could accomodate your set of potential datatypes.
Declare @test-2 int
set @test-2 = 3
-- succeeds because of sql_variant
select case when @test-2 = 1 then cast(.123 as float)
when @test-2 = 2 then 123
when @test-2 = 3 then cast('Alpha' as sql_variant)
else (select cast(0 as bit))
end as result
-- fails on @test-2 = 3 (error converting varchar to float)
select CASE WHEN @test-2 = 1 then cast(.123 as float)
WHEN @test-2 = 2 then 12345
when @test-2 = 3 then 'Alpha'
ELSE cast(0 as bit)
end
Again, the question is why? We've got time for a long story....
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 19, 2009 at 10:08 am
Thank you for the information Bob.
October 19, 2009 at 2:03 pm
You're welcome. Sorry we didn't get to hear the story. 😉
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply