Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2011
    Posts
    5

    Unanswered: Ignore part of where clause if null

    What I am after is a select statement that ignores part of the where condition if either ProductCode or ProductDesc are null, I came up with this but returns nothing
    Code:
    SELECT * FROM [Consumables]
    WHERE (([ProductCode] LIKE '%' + isnull(@prodcode,'') + '%')
    or
    ([ProductDesc] LIKE '%' + isnull(@proddesc,'') + '%'))
    I am using it as part of a SelectCommand in a FormView. Any ideas?

    Thanks, James

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    That's about as good as you're going to get in a single query.
    If you weren't using LIKE, you might get better index performance by unioning two separate select statements.

    But I also have to wonder about your logic. If both parameters are supplied, you still want to do an OR filter rather than AND? Normally if two parameters are supplied it is expected that BOTH must be satisfied.

    And if only one parameter is supplied, you'd be better off with two separate statements.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jun 2011
    Posts
    5
    Hi

    It should be an AND operator.

    Will look into separating them

    thanks

Posting Permissions

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