dragonwell
Programmer
Hi,
warning -the logic here may be flawed (I'm tired...)
I'm trying to create a sp to search a catalog database. The view I'm querying has columns like item-name, size, color, description, etc.
I want the user to be able to submit a search string like 'small red shoe' and return only records that "match". If the search string is 'small shoe', obviously return all those records plus ones of any color.
Assuming I split the searchstring into a table variable, I have something like
the problem is, it returns all the "shoes" regardless of color.
Any ideas on how to get this working are welcome.
warning -the logic here may be flawed (I'm tired...)
I'm trying to create a sp to search a catalog database. The view I'm querying has columns like item-name, size, color, description, etc.
I want the user to be able to submit a search string like 'small red shoe' and return only records that "match". If the search string is 'small shoe', obviously return all those records plus ones of any color.
Assuming I split the searchstring into a table variable, I have something like
Code:
decare @words table(word varchar(100))
insert into @words select '%small%'
insert into @words select '%red%'
insert into @words select '%shoe%'
select * from productview p
join @words w on
(p.itemname like w.word
or p.size like w.word
or p.color like w.word
or p.description like w.word)
the problem is, it returns all the "shoes" regardless of color.
Any ideas on how to get this working are welcome.