Results 1 to 10 of 10

Thread: Select...Case & If...Else!

  1. #1

    Thread Starter
    Frenzied Member arpan_de's Avatar
    Join Date
    Oct 2005
    Location
    Mumbai, India
    Posts
    1,394

    Question Select...Case & If...Else!

    Select...Case & If...Else - are there any advantages/disadvantages of the former over the latter or vice-versa?


    ARPAN

    IF YOU HAVE AN APPLE & I HAVE AN APPLE AND WE EXCHANGE THE APPLES, THEN YOU & I WILL STILL HAVE ONE APPLE BUT IF YOU HAVE AN IDEA & I HAVE AN IDEA AND WE EXCHANGE OUR IDEAS, THEN EACH OF US WILL HAVE TWO IDEAS!

    NOTHING IS IMPOSSIBLE IN THIS WORLD.....EVEN THE WORD IMPOSSIBLE SAYS I'M POSSIBLE!

    PRACTICE MAKES A MAN PERFECT BUT NOBODY IS PERFECT; SO WHY PRACTICE?

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882

    Re: Select...Case & If...Else!

    I believe the perceived efficiency issue is that in a SELECT/CASE with lots of CASE statements that it will jump to the END SELECT after the active CASE is met.

    There is obvious syntax differences - the SELECT/CASE simply looks much better visually with a dozen options.

    There is a kind of "implied hierarchy" to IF/THEN/ELSe/IF...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Frenzied Member arpan_de's Avatar
    Join Date
    Oct 2005
    Location
    Mumbai, India
    Posts
    1,394

    Re: Select...Case & If...Else!

    I believe the perceived efficiency issue is that in a SELECT/CASE with lots of CASE statements that it will jump to the END SELECT after the active CASE is met.
    The same happens in If...Else statements as well; as soon as the condition is satisfied, VB runtime will jump to the End If line.......


    ARPAN

    IF YOU HAVE AN APPLE & I HAVE AN APPLE AND WE EXCHANGE THE APPLES, THEN YOU & I WILL STILL HAVE ONE APPLE BUT IF YOU HAVE AN IDEA & I HAVE AN IDEA AND WE EXCHANGE OUR IDEAS, THEN EACH OF US WILL HAVE TWO IDEAS!

    NOTHING IS IMPOSSIBLE IN THIS WORLD.....EVEN THE WORD IMPOSSIBLE SAYS I'M POSSIBLE!

    PRACTICE MAKES A MAN PERFECT BUT NOBODY IS PERFECT; SO WHY PRACTICE?

  4. #4
    New Member
    Join Date
    Apr 2006
    Posts
    6

    Re: Select...Case & If...Else!

    Switch or Select Statements are more standard in a situation where you have a number of possible cases. There isn't really a performance difference that you would ever notice... it's just one of those things where you have a normal way to write code.

  5. #5
    eltiT resU motsuC Static's Avatar
    Join Date
    Oct 2000
    Location
    Rochester, NY
    Posts
    9,390

    Re: Select...Case & If...Else!

    Select case is more efficient.. as stated... and easier to use in some cases

    Like:

    VB Code:
    1. If X = 10 Or X = 11 Or X = 13 Then
    2.  
    3. ElseIf X = 12 Or (X > 14 And X < 18) Then
    4.  
    5. End If
    6.  
    7. 'where as case..
    8.  
    9. Select Case X
    10.     Case 10, 11, 13
    11.    
    12.     Case 12, 15 To 17
    13.    
    14. End Select
    JPnyc rocks!! (Just ask him!)
    If u have your answer please go to the thread tools and click "Mark Thread Resolved"

  6. #6
    Banned randem's Avatar
    Join Date
    Oct 2002
    Location
    Maui, Hawaii
    Posts
    11,385

    Re: Select...Case & If...Else!

    Think about those who will follow you in coding (or maybe yourself in a few months). It would be easier to figure out what a Select Case statement does than a complex If...then...else statement.

    It just plain reads BETTER!!!!

    Don't Be a Will Allen!!!!!!!!
    Last edited by randem; Apr 28th, 2006 at 04:43 PM.

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882

    Re: Select...Case & If...Else!

    Quote Originally Posted by arpan_de
    The same happens in If...Else statements as well; as soon as the condition is satisfied, VB runtime will jump to the End If line.......
    Not really...

    Code:
    Select Case lngValue
        Case 1
            ...
        Case 2
            ...
        Case 3
            ...
        Case 4
            ...
    End Select
    Cannot be replaced by

    Code:
    If lngValue = 1 Then
       ...
    End If
    
    If lngValue = 2 Then
       ...
    End If
    
    If lngValue = 3 Then
       ...
    End If
    
    If lngValue = 4 Then
       ...
    End If
    Without using GOTO you cannot mimic the SELECT CASE behaviour of "jumping" to the END SELECT with IF/Blocks easily.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882

    Re: Select...Case & If...Else!

    Quote Originally Posted by randem
    Think about those who will follow you in coding (or maybe yourself in a few months). It would be easier to figure out what a Select Case statement does than a complex If...then...else statement.
    I could not agree more - maintainability of code is so much more important at times then simple old efficiency.

    When we have a complex IF statement we sometimes code it like this:

    VB Code:
    1. booDoSomething = True
    2.  
    3. If booDoSomething Then
    4.    If {check some condition to see if we really want to do this} Then
    5.        booDoSomething = False
    6.    End of
    7. End If
    8.  
    9. If booDoSomething Then
    10.    If {check some other condition (2) to see if we really want to do this} Then
    11.        booDoSomething = False
    12.    End of
    13. End If
    14.  
    15. If booDoSomething Then
    16.    If {check some other condition (3) to see if we really want to do this} Then
    17.        booDoSomething = False
    18.    End of
    19. End If
    20.  
    21. If booDoSomething Then
    22.    Call SomeFunction
    23. End If
    This might seem like odd code but the alternative is:

    VB Code:
    1. If {check some condition to see if we really want to do this} _
    2.       or {check some other condition (2) to see if we really want to do this} _
    3.       or {check some other condition (3) to see if we really want to do this} Then
    4.    Call SomeFunction
    5. End If
    This alternative might appear easier to write - but visit it 6 months later and add a NOT condition or an AND/OR condition and watch the bugs fly!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    Frenzied Member arpan_de's Avatar
    Join Date
    Oct 2005
    Location
    Mumbai, India
    Posts
    1,394

    Re: Select...Case & If...Else!

    The same happens in If...Else statements as well; as soon as the condition is satisfied, VB runtime will jump to the End If line.......
    Not really...
    szlamany, what I meant was for a condition like this:
    VB Code:
    1. If lngValue = 1 Then
    2.    ...
    3. ElseIf lngValue = 2 Then
    4.    ...
    5. ElseIf lngValue = 3 Then
    6.    ...
    7. ElseIf lngValue = 4 Then
    8.    ...
    9. End If
    & not for the If code snippet you have cited in post #7. So
    VB Code:
    1. Select Case lngValue
    2.     Case 1
    3.         ...
    4.     Case 2
    5.         ...
    6.     Case 3
    7.         ...
    8.     Case 4
    9.         ...
    10. End Select
    can very well be replaced by the If...Else conditional statements I have shown above. The Select...Case equivalent of the If conditions you have cited in post #7 would be
    VB Code:
    1. Select Case lngValue
    2.     Case 1
    3.         ...
    4. End Select
    5.  
    6. Select Case lngValue
    7.     Case 2
    8.         ...
    9. End Select
    10.  
    11. Select Case lngValue
    12.     Case 3
    13.         ...
    14. End Select
    15.  
    16. Select Case lngValue
    17.     Case 4
    18.         ...
    19. End Select
    Select case is more efficient.. as stated... and easier to use in some cases

    Like:
    Static, you have really shown a very good example to highlight the difference in readability between If....Else & Select...Case statements especially when the conditions turn out to be pretty complex.....great stufffffffff


    ARPAN

    IF YOU HAVE AN APPLE & I HAVE AN APPLE AND WE EXCHANGE THE APPLES, THEN YOU & I WILL STILL HAVE ONE APPLE BUT IF YOU HAVE AN IDEA & I HAVE AN IDEA AND WE EXCHANGE OUR IDEAS, THEN EACH OF US WILL HAVE TWO IDEAS!

    NOTHING IS IMPOSSIBLE IN THIS WORLD.....EVEN THE WORD IMPOSSIBLE SAYS I'M POSSIBLE!

    PRACTICE MAKES A MAN PERFECT BUT NOBODY IS PERFECT; SO WHY PRACTICE?

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,882

    Re: Select...Case & If...Else!

    I understand what you meant now...

    I personally could never use the ELSEIF statement - what I appreciate about IF/THEN/ELSE blocks are the indented nature of them.

    That ELSEIF statement never made any sense to me - makes for some hard to follow logic-flow.
    Last edited by szlamany; Apr 28th, 2006 at 07:09 PM. Reason: oops - typo...

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width