Recently I was faced with the need to search across a table for several search terms and return the results. The search terms were to be built dynamically, so there was no way to know how many would be provided. Due to some client policies, I had to do the work through a stored procedure. I did not want to perform multiple calls to the procedure for each term identified, but you cannot pass multiple parameters to the LIKE statement in SQL, or can you? I immediately thought of passing in all my search terms via XML, but couldn’t remember the syntax to pull the data out. Thankfully a friend of mine was kind enough hear my dilema and not laugh (too hard) when he pointed out that it was called ‘OPENXML’. Yes, extremely obvious, but it had been a long day. Ok, so back on track.
If you haven’t used OPENXML before it is available in SQL Server 2005/2008 and is really quite useful. In the past, I have used this mostly for passing in multiple objects to perform a mass INSERT and/or UPDATE. This was my first time using it with a LIKE statement, but it works perfectly. I didn’t want to lose this code, since I am likely to use it again in the future so I figured I would do a quick blog post. So here is how to pass multiple parameters into a LIKE statement in SQL Server.
Below the schema for a table that I will use in my example.
First, I need to build my search terms in XML form. This is a simple string which can be built in your application code and passed as a parameter into SQL Server. My example below show how you can do it in SQL Server so you can tweak it.
--String parameter with the search terms in XML format
DECLARE @xmlTerms VARCHAR(8000) -- Size it according to your expectations
--Here I am building the XML string with all the necessary terms.
SET @xmlTerms =
--A handle to the XML so we can reference it in the OPENXML call
DECLARE @xmlHandle INT
--This parses the XML and returns us a handle to the data
EXEC sp_xml_preparedocument @xmlHandle out, @xmlTerms
--Now we can perform our query
SELECT id, searchTerm FROM exampleTable A
INNER JOIN (SELECT searchTerm
FROM OPENXML(@xmlHandle,'/SearchTerms/SearchTerm',1) --Make note that the XML nodes are case sensitive
WITH (searchTerm VARCHAR(50) 'text()')) T ON A.Bio LIKE searchTerm
ORDER BY id
One key thing to note is that this must be done via a JOIN, otherwise it will not work. The JOIN is what allows the LIKE comparison over multiple criteria dynamically.