Can I use Case?

  • I am trying to create a re-startable stored procedure.  So I am accepting a parameter that will tell me where to start.

    I'd like to use the following Case statement and expand it.  But it would not let me.

    CASE 

         WHEN @wheretostart = 'Leads' THEN GOTO LEADS

         WHEN @wheretostart = 'AA' THEN GOTO APPENDEDATTRIBUTES

    END 

    So I'm left with using this that works fine.

    IF @wheretostart = 'Leads' GOTO LEADS

    else IF @wheretostart = 'AA' GOTO APPENDEDATTRIBUTES

     

    Now, in Books online it says the CASE statement can be used for Flow of Control statements.  Can anybody shed some light on this?  Why doesn't the CASE statement work? 

     

    Thanks

    Steve

     

  • I believe CASE assigns a value, while IF is flow control using equalities/in-equalities.  That may be why that does not work. 

     

    (I'm sure you already know, GOTO can lead to some messy code...) 

    I wasn't born stupid - I had to study.

  • Case is like a function.

    Can you please provide a link. My Books onlins says this.

    CASE

    Evaluates a list of conditions and returns one of multiple possible result expressions.

    CASE has two formats:

    • The simple CASE function compares an expression to a set of simple expressions to determine the result.
    • The searched CASE function evaluates a set of Boolean expressions to determine the result.

    Both formats support an optional ELSE argument.

    Regards,
    gova

  • Control-of-Flow Language is the section that refers to the Case statement.

     

    And yes I know that GOTO is messy but I don't think there is another way around it in this case.  Basically, the procedure based on the parameter will skip parts of the procedure.  So I'm always GO(TO)ing down the procedure - never back. 

     

    Any suggestions for improvement... let 'em rip.

     

    Thanks

    Steve

     

  • Is this some sort of recurring stored proc?

    Also I'm already gonna say my conclusion. Avoid gotos unless it's for err handling. Just go with a bunch of well documented / separated IFS.

  • Yes, normally I would agree If blocks are the way to go.  In this case, though the normal run just passes through the entire Stored Procedure.  It is a series of table loads.  If I have to start it at a particular table load then I want control to pass to that step and proceed from there.

     

    But maybe I'm rethinking that.  Anyway the Case would still be nice to have Case Blocks instead of If blocks.

     

    Keep on Codin'

    Steve

  • Other Transact-SQL statements that can be used with control-of-flow language statements are:

    CASE

    /*...*/ (Comment)

    -- (Comment)

    DECLARE @local_variable

    EXECUTE

    PRINT

    RAISERROR

     

    This means you can use these with control-of-flow language statements. These are not control-of-flow language statements.

    Regards,
    gova

  • Allow me to throw in a small comment but a BIG advice. Modularize the Code into those pieces create as many procedures as pieces or starting points you need. Then create wrapper proc that calls them in the appropriate workflow without IFs

    ex Supposed you have:

    If @param = Start1 then goto start1

    else If @param = Start2 then goto start2

    else If @param = Start3 then goto start3

    start1:

    .....

    start2:

    ....

    start3:

    ...

    can be translated into:

    create proc proc_start1 ...

    create proc proc_start2 ...

    create proc proc_start3 ...

     

    proc_invoke_all

     begin

      exec proc_start1

      exec proc_start2

      exec proc_start3

     end

    proc_invoke_23

     begin

      exec proc_start2

      exec proc_start3

     end

    OR call them directly if you just need that piece

    if what you need is repetitive and you need speed (who doesn't )

    doit like that!

    Just my $0.02

     

     


    * Noel

  • Just a note to clear things up (or perhaps muddy everything even more )

    Transact SQL CASE is not a switch statement like it is in other languages, it is an expression.

    In short that means that CASE must be able to evalute to true or false, then it returns (as described in BOL)

    Result Types

    Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

     

    Now, there's two small words hidden in here that makes all the differences in the world when working with CASE, and it's good to know about.

    Whenever you write a CASE in either of the ways possible, what is always true is that it will... (quote comes again)

    Returns the highest precedence type from the set of types in result_expressions and the optional else_result_expression.

    What this means is simply that a CASE expression can only return one and only one datatype. If you don't explicitly cast or convert, then the type returned will be determined by the highest precedence type. If the value to be returned cannot be implicitly converted to that type, the statement will then fail.

    This is by far the most common reason when you encounter seemingly 'strange' conversion errors from CASE expressions.

    /Kenneth

     

Viewing 9 posts - 1 through 8 (of 8 total)

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