Every once-in-a-while it would just be convenient to query a single field for multiple values. For example, you want to select from your database the people who live in the states of Arizona, New Mexico, and California. That's no big deal, just do an IN, right? Sure, that works fine, if you know in advance what states you want to look for; but what if you don't? What if a user of your application can pick any number of any of the fifty states to search for?

I guess one could assign a bit value to each state, sum the bit values of the selected states, and then do some bit-wise arithmetic in the SELECT, but what if your list is of more than fifty items? Or the list is of user-defined items that you can't assign a bit value to beforehand? Well, then you can use the method I have outlined here.

To get this to work, you create a delimited string of ID's and pass that into the stored procedure and do a like operation on it in the WHERE clause:

   1: where

   2:     @stateIDString like '%|' CAST(StateID as varchar(max)) + '|%'

This will allow SQL Server to compare the ID's in the table to the delimited string. You can have as many ID's in the string as you need. As you can tell from the example, the string is delimited by pipes ('|') and must both start and end with the pipe (e.g. '|1|34|67|9|'). You do not need to delimit the string with a pipe. As long as you're comparing against a number, it can be anything other than a number.

As you can expect, there are drawbacks to this method. It is not fast. Searching a table with 500,000 rows took about four seconds, and searching a table with one million rows took about nine seconds. I was able to reduce the time by about half by adding a column to the table that was a pre-delimited version of the ID field (e.g. it contained '%|23|%' if the ID was 23). In this case, the like would just contain the name of the field. This field would be easy to set once during the insert operation via the business object, the stored procedure doing the insert, or even a trigger.

I can't take credit for this tip (although the aforementioned optimization is my own idea). A guy that likes to go by the name Homer Sapien introduced this technique to me a while ago. If you happen to know who he is, good for you. If not, your loss. :-)