Multiple parameters in Querystring to serach the Database

I have a form where users can click on the colors check-boxes. These values go to query string like.


This gives values like ColorC=Red, Black, Blue

(1-20 color selections)

My question is that how to search the database by SQL statement. The following will not work in this case. I am using VBscript and MS SQL database.

SQL = "Select * from Products where DESCRIPTION LIKE '%" & ColorC & "%'"

The number of colors are not fixed, there could be 1, 2, 3, 5, 6 colors as selected by a user. And it has LIKE as it finds colors in product description. How does IN work in this case.

couple of options
dynamic sql or use a stored procedure and send in an xml payload of colors


declare @ColorC varchar(50) = 'Red,blue,'
declare @sql varchar(1000)

create table #bab22(id int identity(1,1), vehicle varchar(50), color varchar(50))

insert into #bab22
select 'Milenial Falcon', 'Red' union
select 'Benzo', 'Black' union
select 'Teshla', 'Blue'

set @sql = 'select * From #bab22 where color in (' + replace(QUOTENAME(@ColorC,''''),',', ''',''') + ')'
select @sql

drop table  #bab22

I am using SQL Server 2005. Will this work on it. Or you may describe alternate statements.

Did you try it on SQL 2005?

If you need the LIKE %, you could split the Description into a table of words. Then you can join to a search table.