How to make SQL intersection with table-valued parameter?

Question

I need to make an intersection and get the variantID in common; from a list of parameter pairs (optionID,valueID).

Example: for a given list that has 2 items in it:

• optionID = 16 and valueID = 1
• optionID = 17 and valueID = 4

I wrote below queries manually:

select * 
from tbl_VariantValues
where optionID = 16 and valueID = 1 and productID = 399 

select * 
from tbl_VariantValues
where optionID = 17 and valueID = 4 and productID = 399

I'm getting these results:

productID  variantID  optionID  valueID
---------------------------------------
399        11         16        1
399        12         16        1
399        13         16        1
399        14         16        1
399        15         16        1

productID  variantID  optionID  valueID
---------------------------------------
399        13         17        4
399        19         17        4

As I need variantID only, with intersection:

select variantID 
from tbl_VariantValues
where optionID = 16 
  and valueID = 1 
  and productID = 319 

intersect 

select variantID 
from tbl_VariantValues
where optionID = 17 
  and valueID = 4 
  and productID = 319

I'm getting desired result variantID: 13

The problem is I want to do above query programatically as there can be more items in the tvp list. Is it possible?

I tried to write below query but don't know where and how to put intersect:

create procedure [dbo].[getVariantID]
    (@list OptionValueList readonly)
as
begin 
    declare @UseTVP int
    set @UseTVP = (select count(*) from @list) 

    select variantID
    from dbo.tbl_VariantValues
    where (optionID = (select C.OptionID from @list C) 
      and valueID = (select C.OptionID, C.ValueID from @list C)
       or @UseTVP = 0)
    intersect
end

@UseTVP is the count of the tvp items, I need a logic of intersect usage here to merge the tables. Or is there other way to do this?

Answer 1

One option is use Dynamic SQL to create the query with multiple intersect operator.

Or just use JOIN on VariantValues with the OptionValue. Then use GROUP BY + HAVING to make user all the OptionValue are matches

; with 
--  generate sample table tbl_VariantValues
tbl_VariantValues as
(
    select  productID = 399, variantID = 11, optionID = 16, valueID = 1 union all
    select  productID = 399, variantID = 12, optionID = 16, valueID = 1 union all
    select  productID = 399, variantID = 13, optionID = 16, valueID = 1 union all
    select  productID = 399, variantID = 14, optionID = 16, valueID = 1 union all
    select  productID = 399, variantID = 15, optionID = 16, valueID = 1 union all

    select  productID = 399, variantID = 13, optionID = 17, valueID = 4 union all
    select  productID = 399, variantID = 14, optionID = 17, valueID = 4 union all
    select  productID = 399, variantID = 19, optionID = 17, valueID = 4 union all

    select  productID = 399, variantID = 13, optionID = 18, valueID = 5 union all
    select  productID = 399, variantID = 15, optionID = 18, valueID = 5 union all
    select  productID = 399, variantID = 19, optionID = 18, valueID = 5
),
--  your OptionValue sample
OptionValueList  as
(
    select  productID = 399, optionID = 16, valueID = 1 union all
    select  productID = 399, optionID = 17, valueID = 4 union all
    select  productID = 399, optionID = 18, valueID = 5
)
--  the query
select  v.variantID
from    tbl_VariantValues v
        inner join OptionValueList o    on  v.productID = o.productID
                                       and  v.optionID  = o.optionID
                                       and  v.valueID   = o.valueID
group by v.variantID
having count(*) = (select count(*) from OptionValueList )