November 26, 2004 at 10:44 am
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.
November 29, 2004 at 8:00 am
This was removed by the editor as SPAM
November 29, 2004 at 8:29 am
Maybe additional information would help:
What we are doing is select criteria for a certain vacation. You can have slim criteria (not passing in componentType,ValidItemType,ItemValue) or you can be real specific passing in 1 to many set of criteria.
For example:
and ComponentType = 'H'
and ValidItemType = 'C'
and ItemValue = 'Las Vegas'
Would specify that the Hotel has to be in the city Las Vegas.
Just sending that criteria would bring back a broad range of hotels in that city. You can also additional criteria:
and ComponentType = 'H'
and ValidItemType = 'N'
and ItemValue = 'Tropicana Las Vegas'
This specifys that the Hotel has a name of Tropicana Las Vegas.
I could go on and specify criteria about the room but I think you get the picture.
so for every set of componentType,ValidItemType,ItemValue passed in I need to do another InnerJoin. It needs to be an Inner Join becasue if additional sets of criteria are sent in they need to be met. Doing a union would bring back the rows that meet each individual criteria set. We want to bring back the rows that meet all the criteria set.
Let me know if you need additional Information.
& Thanks for the help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply