T-SQL Query for Vertical Table Structure

Question

I'm working on an e-commerce project. Now I have to build a filter for product listing page. My tables are below.

Products

id title      | description           | Etc.
-- ---------- | --------------------- | -----------
1  Product  1 | Product 1 description | xxx
2  Product  2 | Product 2 description | xxx
3  Product  3 | Product 3 description | xxx
4  Product  4 | Product 4 description | xxx
5  Product  5 | Product 5 description | xxx

Specifications

id title      | Etc.
-- ---------- | ------
1  Color      | xxx
2  Display    | xxx

ProductSpecifications

id          | productId   | specificationId | value
----------- | ----------- | --------------- | -----
1           | 1           | 1               | Red
2           | 1           | 2               | LED
3           | 2           | 1               | Red
4           | 2           | 2               | OLED
5           | 3           | 1               | Blue
6           | 3           | 2               | LED
7           | 4           | 1               | Blue
8           | 4           | 2               | OLED

Users of e-commerce must be able to filter multiple options at the same time. I mean, a user may want to search for "(Red or Blue) and OLED" TVs.

I tried something but i couldn't write the right stored procedure. I guess, i'm stuck here and i need some help.

EDIT :

After some answers, I need to update some additional information here.

The specifications are dynamic. So filters are also dynamic. I generate filters by using a bit column named allowFilter. So I cant use strongly typed parameters like @color or @display

Users may not use filter. Or they may use one or more filter. You can find the query that i'm working on here:

ALTER PROCEDURE [dbo].[ProductsGetAll]
@categoryId int,
@brandIds varchar(max),
@specIds varchar(max),
@specValues varchar(max),
@pageNo int,
@pageSize int,
@status smallint,
@search varchar(255),
@sortOrder smallint
as
/*
TODO: Modify query to use sortOrder
*/
select * into #products
from
(
    select ROW_NUMBER() OVER (order by p.sortOrder) as rowId,p.*
    from Products p left join ProductSpecifications ps on ps.productId = p.id
    where
    (@status = -1
        or (@status = -2 and (p.status = 0 or p.status = 1))
        or (p.status = @status)
    )
    and (@categoryId = -1 or p.categoryId = @categoryId)
    and (@brandIds = '' or p.brandId in (select ID from fnStringToBigIntTable(@brandIds,',')))
    and (
        @search = ''
        or p.title like '%' + @search + '%'
        or p.description like '%' + @search + '%'
        or p.detail like '%' + @search + '%'
    )
    and (@specIds = ''
        or (
            ps.specificationId in (select ID from fnStringToBigIntTable(@specIds,','))
            and ps.value in (@specValues)
        )
    )
) x
where
(rowId > @pageSize * (@pageNo - 1) and rowId <= @pageSize * @pageNo)

select * from #products
select * from Categories where id in (select categoryId from #products)
select * from Brands where id in (select brandId from #products)

select count(p.id)
from Products p left join ProductSpecifications ps on ps.productId = p.id
where 
(@status = -1
    or (@status = -2 and (p.status = 0 or p.status = 1))
    or (p.status = @status)
)
and (@categoryId = -1 or p.categoryId = @categoryId)
and (@brandIds = '' or p.brandId in (select ID from fnStringToBigIntTable(@brandIds,',')))
and (
    @search = ''
    or p.title like '%' + @search + '%'
    or p.description like '%' + @search + '%'
    or p.detail like '%' + @search + '%'
)
and (@specIds = ''
    or (
        ps.specificationId in (select ID from fnStringToBigIntTable(@specIds,','))
        and ps.value in (@specValues)
    )
)

drop table #products

My problem is the part of:

and (@specIds = ''
        or (
            ps.specificationId in (select ID from fnStringToBigIntTable(@specIds,','))
            and ps.value in (@specValues)
        )
    )

I can totally change of this part and the parameters that used in this part.

Answer 1

Firstly, I have to thank you @alex. I used table valued paramters to solve my problem.

Type:

CREATE TYPE [dbo].[specificationsFilter] AS TABLE(
    [specId] [int] NULL,
    [specValue] [varchar](50) NULL
)

Stored Procedure:

ALTER PROCEDURE [dbo].[ProductsGetAll]
@categoryId int,
@brandIds varchar(max),
@specifications specificationsFilter readonly,
@pageNo int,
@pageSize int,
@status smallint,
@search varchar(255),
@sortOrder smallint
as
declare @filterCount int
set @filterCount = (select count(distinct specId) from @specifications)
/*
ORDER BY
    TODO: Modify query to use sortOrder
*/
select * into #products
from
(
    select ROW_NUMBER() OVER (order by p.sortOrder) as rowId,p.*
    from Products p
    where
    (@status = -1
        or (@status = -2 and (p.status = 0 or p.status = 1))
        or (p.status = @status)
    )
    and (@categoryId = -1 or p.categoryId = @categoryId)
    and (@brandIds = '' or p.brandId in (select ID from fnStringToBigIntTable(@brandIds,',')))
    and (
        @search = ''
        or p.title like '%' + @search + '%'
        or p.description like '%' + @search + '%'
        or p.detail like '%' + @search + '%'
    )
    and (@filterCount = 0
        or (
            p.id in (
                select productId
                from ProductSpecifications ps, @specifications s
                where
                ps.specificationId = s.specId
                and ps.value = s.specValue
                group by productId
                having sum(1) >= @filterCount
            )
        )
    )
) x
where
(rowId > @pageSize * (@pageNo - 1) and rowId <= @pageSize * @pageNo)

select * from #products
select * from Categories where id in (select categoryId from #products)
select * from Brands where id in (select brandId from #products)

select count(p.id)
from Products p
where 
(@status = -1
    or (@status = -2 and (p.status = 0 or p.status = 1))
    or (p.status = @status)
)
and (@categoryId = -1 or p.categoryId = @categoryId)
and (@brandIds = '' or p.brandId in (select ID from fnStringToBigIntTable(@brandIds,',')))
and (
    @search = ''
    or p.title like '%' + @search + '%'
    or p.description like '%' + @search + '%'
    or p.detail like '%' + @search + '%'
)
and (@filterCount = 0
    or (
        p.id in (
            select productId
            from ProductSpecifications ps, @specifications s
            where
            ps.specificationId = s.specId
            and ps.value = s.specValue
            group by productId
            having sum(1) >= @filterCount
        )
    )
)

drop table #products

.Net Code to create Data Table paramter:

    private DataTable GetSpecificationFilter(string specificationFilter)
    {
        DataTable table = new DataTable();
        table.Columns.Add("specId", typeof(Int32));
        table.Columns.Add("specValue", typeof(string));

        string[] specifications = specificationFilter.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
        foreach(string specification in specifications)
        {
            string[] specificationParams = specification.Split(new char[] { ':' }, StringSplitOptions.RemoveEmptyEntries);
            int specificationId = Convert.ToInt32(specificationParams[0]);
            string[] specificationValues = specificationParams[1].Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
            foreach(string value in specificationValues)
            {
                table.Rows.Add(specificationId, value);
            }
        }
        return table;
    }

And my query string structure:

?specs=1:Red,Blue;3:LED,OLED

This is a complete solution to filter product specifications in a vertical table sturcture. I used this for an e-commerce project. I hope this solution helps you for similar cases.