Searching a table with SQL LIKE statement and multiple paramters

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.

Here is a little sample data for as well


So I need to be able to search the “Bio” field in my table to see if it contains the words ‘PhD’, ‘scientist’ or ‘CEO’.

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 =

'<SearchTerms>

<SearchTerm>%PhD%</SearchTerm>

<SearchTerm>%scientist%</SearchTerm>

<SearchTerm>%CEO%</SearchTerm>

<SearchTerms>'



--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.

SQL Server 2008 – Preventing Database Changes

I was laying out a database for an application today with SQL Server 2008. Now this is my first encounter with this latest release so I am expecting everything to work as it has in the past. I thought I would share a quick note on a new piece of functionality, that I was surprised by. I am doing a quick dump on what happened and do not claim to have in-depth knowledge of this latest release. So I created my tables using the Database Diagram so I could throw them together quickly and build all my relationships. Well that was done in no time and everything was created as expected. It was when I started down the path to apply a tweak that I ran into something I never expected. After making a change specifying a column as an Identity column, I click the X to close the screen and choose to save the change. I was promptly greeted by the following message.


My first thought is, “NO SQL Server you are wrong, I will cancel and hit Save again to show you”. Why, because I didn’t read the message. Well SQL Server showed me and let me know that I hit cancel, see below.


Unfortunately I was not returned to the table designer because I had selected Close. Thankfully that was low impact for me since I only made a single modification, but if I had made several I would have been irritated. Ok, so I go back into the designer and save. Ahhh, SQL Server is protecting me from breaking something, nice. This is definitely a good feature on a production database to keep the riff raff from bringing things to a halt. I see a Save Text File button and I bet that will output a create script with my change so I can apply it safely. I click the button, Yippee, save the file and open it up. What would you expect to happen? Well if you thought like me, you would be wrong. Check out what was output.


What is that? Joy! Joy! this is great, I get text file on my system that is completely useless. Now granted there may be something where you can set up a script to be output, this is my first roll in the hay with SQL Server 2008, but would that not be on by default. If this is all the Save Text File button does though, I would ask why have it. How is that capability useful to me if there is a restriction on saving my change to the database and I cannot find a way around it. If you are aware of something I am missing, please share.

Well, I knew I didn’t want to be hindered by this any longer so I found the setting to turn off this functionality. I suspect this will be a common complaint from people who install SQL Server 2008 the first time. So to change it navigate through the Tools – Options menu and select the Designers entry on the left. Then simply uncheck the “Prevent saving changes that require table re-creation” setting.

Now you can work as you have in the past. Saving all those little modifications that could potentially break something. Again, I think the setting is a good idea to have on a production system. I have seen people apply changes to production without care and cause problems across several applications. If you are working on your development system it will be a pain and you probably just want to turn it off. Very quickly, here is the link to this functionality on SQL Server Books OnLine.