On a site I have a search box.
Currently the way this works is the user enters a value and I append % to the start and end of the value and search against the appropriate columns.
However this sort of basic search will miss a lot of values. For example a search of "term1 and term2" will miss a value of "term1, term2". What I would like to do is pass each word in to my stored procedure.
I believe the best way to do this is using OPENXML. My Stored Procedure currently looks like this
SELECT blah
FROM Company
WHERE name LIKE
(
SELECT SearchTerm
FROM OPENXML (@hdoc, '/File/Item', 1)
WITH (SearchTerm nvarchar(20))
)
OR desc LIKE
(
SELECT SearchTerm
FROM OPENXML (@hdoc, '/File/Item', 1)
WITH (SearchTerm nvarchar(20))
)
However if I have more than one value in my XML file I get an error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So I tried changing the LIKE clause in the query to IN, which removed the error, but now only exact matches are returned.
Has anybody got a suggestion of what I should be doing?
Currently the way this works is the user enters a value and I append % to the start and end of the value and search against the appropriate columns.
However this sort of basic search will miss a lot of values. For example a search of "term1 and term2" will miss a value of "term1, term2". What I would like to do is pass each word in to my stored procedure.
I believe the best way to do this is using OPENXML. My Stored Procedure currently looks like this
SELECT blah
FROM Company
WHERE name LIKE
(
SELECT SearchTerm
FROM OPENXML (@hdoc, '/File/Item', 1)
WITH (SearchTerm nvarchar(20))
)
OR desc LIKE
(
SELECT SearchTerm
FROM OPENXML (@hdoc, '/File/Item', 1)
WITH (SearchTerm nvarchar(20))
)
However if I have more than one value in my XML file I get an error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
So I tried changing the LIKE clause in the query to IN, which removed the error, but now only exact matches are returned.
Has anybody got a suggestion of what I should be doing?