Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    33

    Unanswered: Where clause with multiple cases

    I have a table with a field that contains an integer which represents the state of a record. This field "intType" may contain values 0-4.

    A parameter in my stored procedure "@intUserType" may contain values 0-3

    If @intUserType = 0, I need to select the records where intType = 0 or 3 but if @intUserType = 3, I need to return all records where intType > 1, all other values of @intUserType should return no records

    The query I am working with seems a bit forced and I feel like it could be simplified, but I can't seem to wrap my head around it.

    This is what I am working with:
    Code:
    SELECT * FROM tblEmployees
    WHERE (intType = (CASE WHEN @intUserType = 0 THEN 0 ELSE NULL END)
         OR intType = (CASE WHEN @intUserType = 0 THEN 3 ELSE NULL END)
         OR intType > (CASE WHEN @intUserType = 3 THEN 1 ELSE NULL END))
    Maybe it is as good as it needs to be ... I don't know .. I've only been using SQL regulary for a couple of months and I have not had the time to really study it in depth.

    Any suggestions for improvements or should I just leave it alone?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This logic seems more straight-forward to me:
    Code:
    Where	intType = 0 and @intUserType = 0
    	or intType = 3 and @intUserType = 0
    	or intType > 1 and @intUserType = 3
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2009
    Posts
    33
    I knew it had to be simpler than what I was looking at ... I can't imagine why I didn't see it. ;-)

    That should also execute a bit faster.

    Do I need to wrap the and's in parens like this:
    Code:
    Where	(intType = 0 and @intUserType = 0)
             or (intType = 3 and @intUserType = 0)
             or (intType > 1 and @intUserType = 3)
    Thanks!
    Last edited by keb1965; 11-28-12 at 12:17.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The parenthesis are not necessary in this specific case, as the predicate types are identical. Where you to throw in some AND predicates, then parenthesis would be necessary to define the order of evaluation.
    You can use parenthesis for clarity if you'd like, but I'd avoid enclosing the entire WHERE clause in superfluous parenthesis as you did in the first code you posted. Redundant parenthesis actually make code more difficult to decipher.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2009
    Posts
    33
    Got it ..

    The reason the original code contained parens is because I didn't post the entire query. the original query joins on five additional tables and has 10 ANDs involved

    I've applied the syntax you provided throughout my query and it is working fine ... much easier to read and understand too!

    I guess that is what happens when as you gain experience. ;-)

Posting Permissions

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