Case Statement??

  • 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.

  • 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

  • I guess the real question would be......why? Why would you ever need to do this?

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • 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??

  • 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 CASEWHEN @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

  • Thank you for the information Bob.

  • 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