Multiple statements in case for SQL Server

  • sasank.rachagundla

    SSC Enthusiast

    Points: 168

    Hello everyone,

    I have a requirement, where i need to set some values in case statements

    like

    This statement

    CASE ISNULL([Column 0],'')

    WHEN '' THEN 'NOT A NULL FIELD'

    ELSE [Column 0] + ' CITY'

    END

    AS testLOCATION

    is working fine in a select statement.

    But my requirement is this:

    DECLARE @test VARCHAR(10)

    (CASE ISNULL([Column 0],'')

    WHEN

    BEGIN

    SET @test =’TESTVALUE’

    END

    ''

    THEN

    BEGIN

    'At Location' + ISNULL([Column 0],'')

    END

    ELSE CAST([Column 4] AS VARCHAR) +' KMS ' + RTRIM(ISNULL([Column 0],'')) + ' OF ' +' CITY'

    How can i fix this

  • John Mitchell-245523

    SSC Guru

    Points: 148259

    I'm sorry, but I don't understand what you're trying to do. Why do you think you need to use a variable, and why do you set a value for that variable but not use it subsequently?

    John

  • davidandrews13

    SSCertifiable

    Points: 6660

    you want to evaluate two conditions in the same case statement like this?

    select case when 1 = 1 and 2 = 2 then 'both' else 'not both' end

  • sasank.rachagundla

    SSC Enthusiast

    Points: 168

    Thanks for the prompt reply.

    However this is the equivalent C# code

    If ([column1]is NULL)

    Set [column1]= string.Empty;

    If([column1]is NULL)

    Set [column1] = 0;

    If([column3]is NULL)

    Set [column3]= string.Empty;

    Switch(([column1])

    {

    ([column1]equals string.Empty:

    return ‘At ’ + ([column3];

    default://for all other cases

    return Convert.ToString(Round(([column2],4))

    + ‘ kms ’

    + Rtrim(([column0])

    + ‘ of ’

    + ([column3];

    }

  • Iulian -207023

    SSCertifiable

    Points: 7507

    Hi, You can try something like:

    SELECT

    ISNULL([column1], '') AS A

    , ISNULL([column1], 0) AS B

    , ISNULL([column3], '') AS C

    , CASE WHEN ISNULL([Column 1],'') = '' THEN 'At ' + ISNULL([Column 3],'')

    ELSE CAST([Column 4] AS VARCHAR) +' KMS ' + RTRIM(ISNULL([Column 0],'')) + ' OF ' +[column3]

    END

    FROM table_name

    Regards,

    Iulian

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply