Combined search problems in E-commerce

When searching on an e-commerce website, you often need to make a Combined Query of various conditions, such as the year, price, color, brand, and so on. The results that meet the conditions must be output. I tried to write an SQL function to implement this kind of dynamic combination query, but it still could not be fully implemented, but it could only implement a fixed combination query in 2, hope that you can see the following code to help implement the following or provide the following ideas:

/* Separate the characters into a list based on the delimiter in the string and return the results through table */
Create Function [DBO]. [fn_split] (@ inputstr nvarchar (4000), @ seprator nvarchar (10 ))
Returns @ temp table (A nvarchar (200 ))
As
Begin
Declare @ I int
Set @ inputstr = rtrim (ltrim (@ inputstr ))
Set @ I = charindex (@ seprator, @ inputstr)
While @ I> = 1
Begin
Insert @ temp values (left (@ inputstr, @ I-1 ))
Set @ inputstr = substring (@ inputstr, @ I + 1, Len (@ inputstr)-@ I)
Set @ I = charindex (@ seprator, @ inputstr)
End
If @ inputstr <> '\'
Insert @ temp values (@ inputstr)
Return
End

 

/* Parse the separators in the input string to obtain the search conditions,
Query in the t_ B _productattr table based on these search conditions.
Currently, the function only supports querying strings composed of two or three conditions.
I spent 2 hours trying to complete the query that can break the string into N conditions.
Failed. I hope you can help extend the query to break the string into N conditions.
*/
Create Function [DBO]. [fn_getproductidbyattvalue]
(
@ Attval nvarchar (200), -- enter a condition character
@ Num int -- number of condition attributes
)
Returns @ temp table (A nvarchar (200 ))
As
Begin
-- Declare @ temp table (A nvarchar (200 ))
-- Declare a temporary table to store input condition values
Declare @ tempattvalue table (rownum int, A nvarchar (200 ))
-- Store the condition value in the temporary table and add the row number
Insert into @ tempattvalue
Select row_number () over (order by a),
From DBO. fn_split (@ attval ,',')
-- If the number of conditions is 2
If @ num = 2
Begin
-- Declare attribute parameters and assign values to them
Declare @ attval1 nvarchar (30)
Declare @ attval2 nvarchar (30)
Select @ attval1 = A from @ tempattvalue
Where rownum = 1
Select @ attval2 = A from @ tempattvalue
Where rownum = 2
-- Get the query result and return it
Insert into @ temp
Select productid from t_ B _productattr
Where attrvalue = @ attval1 and productid in (
Select productid from t_ B _productattr
Where attrvalue = @ attval2)
End
Else if @ num = 3
Begin
-- Declare attribute parameters and assign values to them
Declare @ attval4 nvarchar (30)
Declare @ attval5 nvarchar (30)
Declare @ attval6 nvarchar (30)
Select @ attval4 = A from @ tempattvalue
Where rownum = 1
Select @ attval5 = A from @ tempattvalue
Where rownum = 2
Select @ attval6 = A from @ tempattvalue
Where rownum = 3
-- Get the query result and return it
Insert into @ temp
Select productid from t_ B _productattr
Where attrvalue = @ attval4 and productid in (
Select productid from t_ B _productattr
Where attrvalue = @ attval5 and productid in (
Select productid from t_ B _productattr
Where attrvalue = @ attval6
))
End
Return
End