SQL Server 2008: если несколько значений заданы в других значениях Mutliple

Question

В SQL, есть ли способ сделать что-то подобное? Это основано на отчете, построенном в построителе отчетов SQL Server, где пользователь может указать несколько текстовых значений в качестве одного параметра отчета. Запрос для отчета захватывает все значения, выбранные пользователем, и сохраняет их в одной переменной. Мне нужен способ, чтобы запрос возвращал только записи, имеющие ассоциации с КАЖДЫМ значением, указанным пользователем.

-- Assume there's a table of Elements with thousands of entries.  
-- Now we declare a list of properties for those Elements to be associated with.

create table #masterTable (
    ElementId int, Text varchar(10)
)

insert into #masterTable (ElementId, Text) values (1, 'Red');
insert into #masterTable (ElementId, Text) values (1, 'Coarse');
insert into #masterTable (ElementId, Text) values (1, 'Dense');
insert into #masterTable (ElementId, Text) values (2, 'Red');
insert into #masterTable (ElementId, Text) values (2, 'Smooth');
insert into #masterTable (ElementId, Text) values (2, 'Hollow');

-- Element 1 is Red, Coarse, and Dense.  Element 2 is Red, Smooth, and Hollow.  
-- The real table is actually much much larger than this; this is just an example.

-- This is me trying to replicate how SQL Server Report Builder treats 
-- report parameters in its queries.  The user selects one, some, all, 
-- or no properties from a list. The written query treats the user's   
-- selections as a single variable called @Properties.
-- Example scenario 1: User only wants to see Elements that are BOTH Red and Dense.
select e.* 
from Elements e 
where (@Properties) --ideally a set containing only Red and Dense
in
(select Text from #masterTable where ElementId = e.Id) --ideally a set containing only Red, Coarse, and Dense
--Both Red and Dense are within Element 1's properties (Red, Coarse, Dense), so Element 1 gets returned, but not Element 2.

-- Example scenario 2: User only wants to see Elements that are BOTH Red and Hollow.  
select e.* from Elements e where
(@Properties) --ideally a set containing only Red and Hollow
in
(select Text from #masterTable where ElementId = e.Id)
--Both Red and Hollow are within Element 2's properties (Red, Smooth, Hollow), so Element 2 gets returned, but not Element 1.


--Example Scenario 3: User only picked the Red option.
select e.* from Elements e where
(@Properties) --ideally a set containing only Red
in
(select Text from #masterTable where ElementId = e.Id)
--Red is within both Element 1 and Element 2's properties, so both Element 1 and Element 2 get returned.

Приведенный выше синтаксис на самом деле не работает, потому что SQL, кажется, не допускает множественных значений в левой части сравнения "in". Ошибка, которая возвращает:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Я даже на правильном пути здесь? Извините, если пример выглядит многословно или запутанно.

Вот точный код, с которым я работаю:

select p.*
from Products p
where
(
    (
        --user can search through gloves, bats, or both
        p.TypeId = 2 and 'Bat' in (@ProductTypes) 
        and 
        (
            (
                (@BatProperties) COLLATE DATABASE_DEFAULT in 
                (
                    select props.Name from PropertyTypes props
                    inner join ProductProperties pp on props.Id = pp.TypeId
                    where pp.ProductId = p.Id
                )
            --still want query to run when no properties are selected
            ) or not exists(select * from @BatProperties)
        )
    )
    or
    (
        p.TypeId = 1 and 'Glove' in (@ProductTypes) --user can search through gloves, bats, or both
        and
        (
            (
                (@GloveProperties) COLLATE DATABASE_DEFAULT in 
                (
                    select props.Name from PropertyTypes props
                    inner join ProductProperties pp on props.Id = pp.TypeId
                    where pp.ProductId = p.Id
                )
            --still want query to run when no properties are selected
            ) or not exists(select * from @GloveProperties)
        )
    )
)

Answer 1

Предполагая, что текстовое поле элемента не содержит запятых, вы можете сначала токенизировать строку параметра и заполнить свойства во временной таблице, которая впоследствии может быть объединена с #masterTable для получения элементов со всеми желаемыми свойствами. CTE может использоваться для токенизации, как показано ниже (код не является стандартом производства)

declare @parameter varchar(1000)
declare @num_of_params int
set @num_of_params = 0
set @parameter = 'Red, Dense, Coarse'
CREATE TABLE #tmp (id int, string varchar(1000))

INSERT INTO #tmp (id, string)
SELECT 1, @parameter 

;WITH test (id, lft, rght, idx)
AS 
(
    SELECT t.id
        ,LEFT(t.string, CHARINDEX(', ', t.string) - 1)
        ,SUBSTRING(t.string, CHARINDEX(', ', t.string) + 2, DATALENGTH(t.string))
        ,0
    FROM #tmp t
    UNION ALL
    SELECT c.id
        ,CASE WHEN CHARINDEX(', ', c.rght) = 0 THEN c.rght ELSE LEFT(c.rght, CHARINDEX(', ', c.rght) - 1) END
        ,CASE WHEN CHARINDEX(', ', c.rght) > 0 THEN SUBSTRING(c.rght, CHARINDEX(', ', c.rght) + 2, DATALENGTH(c.rght)) 
            ELSE '' END
        ,idx + 1
    FROM test c 
    WHERE DATALENGTH(c.rght) > 0
)

select * into #test from test
select @num_of_params = count(*) from #test
Drop table #tmp
select @num_of_params


select e.* from Elements e where elementID in (
select elementID
from #masterTable  m inner join #test t 
on m.text = t.lft
group by m.ElementID
having count(*) = @num_of_params
)

drop table #test