Multiple parameters in Querystring to serach the Database

1

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

http://www.example.com/page?Color=Red&Color=Black&Color=Blue

ColorC=Request.QueryString("Color")

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.

2

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

3

oogly

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
exec(@sql)



drop table  #bab22
4

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

5

Did you try it on SQL 2005?

6

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