Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2002
    Posts
    45

    Unanswered: Adding optional criteria in a select proc

    I would like to write 1 proc that can take additional criteria if its sent in. An example is:

    select HA.PriceId, HA.VendorPackageId from Criteria HA Inner Join
    (
    select VendorPackageId from ValidVendorPackages
    where Vendor = @VENDOR
    and Sitecode = @SITECODE
    and PackageType = @PACKAGETYPE
    )HB on HA.VendorPackageId = HB.VendorPackageId
    and CriteriaId in
    (
    select CriteriaID from ValidItemCriteria
    where Destination = @DESTINATION
    and LengthOfStay = @LENGTHOFSTAY
    and Ages = @AGE
    and ComponentType = @COMPONENTTYPE_1
    and ValidItemType = @VALIDITEMTYPE_1
    and ItemValue = @ITEMVALUE_1
    )


    Multiple @COMPONENTTYPE, @VALIDITEMTYPE,@ITEMVALUE can be sent in.
    Instead of making multiple procs or copying the proc multiple times with an if statement at the top checking the number of parameters that aren't =''. Is there a way to exectue:

    and CriteriaId in
    (
    select CriteriaID from ValidItemCriteria
    where Destination = @DESTINATION
    and LengthOfStay = @LENGTHOFSTAY
    and Ages = @AGE
    and ComponentType = @COMPONENTTYPE_1
    and ValidItemType = @VALIDITEMTYPE_1
    and ItemValue = @ITEMVALUE_1
    )
    and CriteriaId in
    (
    select CriteriaID from ValidItemCriteria
    where Destination = @DESTINATION
    and LengthOfStay = @LENGTHOFSTAY
    and Ages = @AGE
    and ComponentType = @COMPONENTTYPE_2
    and ValidItemType = @VALIDITEMTYPE_2
    and ItemValue = @ITEMVALUE_2
    )
    and CriteriaId in
    (
    select CriteriaID from ValidItemCriteria
    where Destination = @DESTINATION
    and LengthOfStay = @LENGTHOFSTAY
    and Ages = @AGE
    and ComponentType = @COMPONENTTYPE_3
    and ValidItemType = @VALIDITEMTYPE_3
    and ItemValue = @ITEMVALUE_3
    )

    Ignoring the 2nd 2 selects if @COMPONENTTYPE_2, @VALIDITEMTYPE_2,@ITEMVALUE_2 and @COMPONENTTYPE_3, @VALIDITEMTYPE_3,@ITEMVALUE_3 are = ''

    Thanks for your help in advance.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yes, there are ways to do this, but I haven't seen any generic way. It requires knowledge of the conditions that apply (especially how your code needs to handle incomplete sets of criteria, such as when only the second value for @COMPONENTTYPE is supplied without the second @VALIDITEMTYPE or @ITEMVALUE ).

    I've never found a satisfactory generic way to handle this kind of problem.

    -PatP

  3. #3
    Join Date
    Dec 2002
    Posts
    45
    Quote Originally Posted by Pat Phelan
    Yes, there are ways to do this, but I haven't seen any generic way. It requires knowledge of the conditions that apply (especially how your code needs to handle incomplete sets of criteria, such as when only the second value for @COMPONENTTYPE is supplied without the second @VALIDITEMTYPE or @ITEMVALUE ).

    I've never found a satisfactory generic way to handle this kind of problem.

    -PatP
    You will never have a 2nd and not a first.

    You'll either have 1, 2 or 3 of
    @COMPONENTTYPE
    @VALIDITEMTYPE
    @ITEMVALUE

    if they send in all 3 @ITEMVALUE_1 thourgh 3 will be populated and so will @VALIDITEMTYPE & @ITEMVALUE

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is this close to what you want? I hope you are not expecting any conditional search such as this to run particularly fast...

    select Distinct Criteria.PriceId, Criteria.VendorPackageId
    from Criteria
    Inner Join ValidVendorPackages
    on Criteria.VendorPackageId = ValidVendorPackages.VendorPackageId
    and ValidVendorPackages.Vendor = @VENDOR
    and ValidVendorPackages.Sitecode = @SITECODE
    and ValidVendorPackages.PackageType = @PACKAGETYPE
    Left Outer Join ValidItemCriteria VIC_1
    on Criteria.CriteriaID = VIC_1.CriteriaID
    and VIC_1.Destination = @DESTINATION
    and VIC_1.LengthOfStay = @LENGTHOFSTAY
    and VIC_1.Ages = @AGE
    and VIC_1.ComponentType = @COMPONENTTYPE_1
    and VIC_1.ValidItemType = @VALIDITEMTYPE_1
    and VIC_1.ItemValue = @ITEMVALUE_1
    Left Outer Join ValidItemCriteria VIC_2
    on Criteria.CriteriaID = VIC_2.CriteriaID
    and VIC_2.Destination = @DESTINATION
    and VIC_2.LengthOfStay = @LENGTHOFSTAY
    and VIC_2.Ages = @AGE
    and VIC_2.ComponentType = @COMPONENTTYPE_2
    and VIC_2.ValidItemType = @VALIDITEMTYPE_2
    and VIC_2.ItemValue = @ITEMVALUE_2
    Left Outer Join ValidItemCriteria VIC_3
    on Criteria.CriteriaID = VIC_1.CriteriaID
    and VIC_3.Destination = @DESTINATION
    and VIC_3.LengthOfStay = @LENGTHOFSTAY
    and VIC_3.Ages = @AGE
    and VIC_3.ComponentType = @COMPONENTTYPE_3
    and VIC_3.ValidItemType = @VALIDITEMTYPE_3
    and VIC_3.ItemValue = @ITEMVALUE_3
    where VIC_1.CriteriaID is not null
    or VIC_2.CriteriaID is not null
    or VIC_3.CriteriaID is not null
    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 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Are the matches against collections of criteria (for example, a given row needs to match any one of the vendors, any one of the site codes, and any one of the item values in order to qualify), or are the matches against sets of criteria (a row needs to match on vendor N, site code N, and criteria N in order to qualify)? That makes a considerable difference in how the code needs to work.

    Do NULL values matter (do you ever need to search for a NULL criteria)? That's a really nasty twist from a performance perspective.

    -PatP

  6. #6
    Join Date
    Dec 2002
    Posts
    45

    The Solution I used

    SELECT
    ...
    WHERE
    ...
    and CriteriaID in
    (
    select CriteriaID from ValidItemCriteria
    where Destination = @DESTINATION
    and LengthOfStay = @LENGTHOFSTAY
    and Ages = @AGE
    and
    (
    coalesce(@COMPONENTTYPE_1, @VALIDITEMTYPE_1, @ITEMVALUE_1) is null
    OR
    (ComponentType = @COMPONENTTYPE_1
    and ValidItemType = @VALIDITEMTYPE_1
    and ItemValue = @ITEMVALUE_1)
    )
    and
    (
    coalesce(@COMPONENTTYPE_2, @VALIDITEMTYPE_2, @ITEMVALUE_2) is null
    OR
    (ComponentType = @COMPONENTTYPE_2
    and ValidItemType = @VALIDITEMTYPE_2
    and ItemValue = @ITEMVALUE_2)
    )
    and
    (
    coalesce(@COMPONENTTYPE_3, @VALIDITEMTYPE_3, @ITEMVALUE_3) is null
    OR
    (ComponentType = @COMPONENTTYPE_3
    and ValidItemType = @VALIDITEMTYPE_3
    and ItemValue = @ITEMVALUE_3)
    )
    )

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm just curious, but would you please explain what you think that SQL will do?

    -PatP

  8. #8
    Join Date
    Dec 2002
    Posts
    45

    Lol

    Quote Originally Posted by Pat Phelan
    I'm just curious, but would you please explain what you think that SQL will do?

    -PatP
    Its useless, I noticed the flaw myself. I realized that I was negating the first criteria if I found criteria on the second. I have changed the question to a new post with a query I belive will complish this:Optional Inner Joins.

    Please help if you can.

Posting Permissions

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