We’ve had to generate a search facility for books on a site. The user types in any number of values and the results are ranked based on how many of the terms are actually found.
The example below has a hard coded XML data, but obviously this would be passed in as a parameter, and as you build this externally you can include as many terms as you need.
DECLARE @xml XML
SET @xml = '<search>
<term text="and" />
<term text="the" />
<term text="princess" />
<term text="9780719554285" />
</search>';
/* Use a CTE to load the terms into a table for later joining */
With MatchingTerms (Term)
AS ( Select node.value('./@text', 'varchar(2000)') AS Term
FROM @xml.nodes(N'//term') T(node))
SELECT b.id,
b.coverTitle,
b.ISBN13,
shortName as [binding],
'B' as [type],
count(id) as score
FROM Book b,
MatchingTerms x
WHERE
b.coverTitle like '%' + x.Term + '%'
OR
b.ISBN13 = x.Term
GROUP BY
b.id, b.coverTitle, b.ISBN13, shortName
