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.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|||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|||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|||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|||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)
)
)|||I'm just curious, but would you please explain what you think that SQL will do?

-PatP|||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.