case statement in sql server

  • Hi, in my sp, I need to do the following logic:

    while ( @mv_i <= @mv_count )

    begin

    select @mv_dummy = item

    from mv_table

    -- then base on the @mv_dummy's value

    do something

    -- for example:

    when @mv_dummy = 1

    update table_1

    when @mv_dummy = 2

    update table_2

    when @mv_dummy = 3

    call another_sp

    I am thinking if I can use a case statement like this:

    case item

    when 1

    then update table_1

    when 2

    then update table_2

    when 3

    then call_another_sp

    else

    do somthing else

    end

    end

    But when I compile the code, it always complaints syntax error. If I simply return a value in case statement, then it is O.K.

    I am wondering if I can do some complicated logic inside case statement (like update or call other function), as what we can do in C language.

    I haven't seen elsif statement in sql server.

    Looks like I can only do:

    if ...

    begin

    end

    else

    begin

    if

    begin

    end

    else

    begin

    end

    .....

    Correct me if I have the wrong impression, since I am new to sql server.

    All I want to do is, either use a case statement or if, elsif statement in sql server. Any recommendation is appreciated.

    Abby

  • update mytable

    set myvalue =

    case

    when mv_dummy = 1

    then "a"

    when mv_dummy = 2

    then "b"

    else "c"

    end

    where Mytable.MyPK = @x

    Steve Jones

    steve@dkranch.net

  • Hi, Thanks for the reply. What I really would like to know is

    when @mv_dummy = 1,

    update table_1

    when @mv_dummy = 2

    update table_2

    when @mv_dummy = 3

    do_something_else_like_calling_a_sp,

    Table_1 is not the same as table_2.

    Can I do that?

    Thanks.

    Abby

  • No, then you're really trying to do something a bit different. You can do it by building the sql and passing it to Exec(). In other words, the case will help you build the string, but not be included in the string.

    Andy

  • well you can use else if you carry out your validations

    if condition1

    BEGIN

    -- do something

    END

    else if condition2

    BEGIN

    -- do something 2

    END

    else if condition3

    BEGIN

    -- do something 3

    END

    Hope this solves your problem.

    Paras Shah

    Evision Technologies

    Mumbai, India


    Paras Shah
    Evision Technologies
    Mumbai, India

  • Hi, Thanks. The 'else if' clause resolves my problem. I though SQL Server doesn't have the equivalence for elsif in oracle, since

    in my example book, it only has 'if ... else... if... else', and I was an oracle db programmer and trying to port some code to sql server.

    Great Thanks.

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

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