Searching a single column by multiple values

We are often faced with a requirement of searching a single column by multiple values.  For example, one of my customers wanted to pass a list of part numbers to an inventory database and get all the part information for that list.  There are several good ways of accomplishing this, but one way I discovered that I like a lot is to accept a comma delimited list as the parameter and then convert it to XML to be used in your query.  Better yet, you could simply accept XML as the parameter.  I’ll use an example of the former methodology though, because I can see a lot of practical uses for it such as building a stored procedure and letting a user pass such as list to SSRS, whereas using XML in that scenario would impractical (unless it was done programmatically in which case would be the ideal way of doing it).

DECLARE @EmployeeIds varchar(8000)
SET @EmployeeIds = '1,4,5,6'

DECLARE @Xdoc int
DECLARE @EmployeeIdXml varchar(8000)
SET @EmployeeIdXml = '<Employees><Employee EmployeeId="' + REPLACE( @EmployeeIds, ',', '"></Employee><Employee EmployeeId="') + '"></Employee></Employees>'

EXEC sp_xml_preparedocument @Xdoc OUTPUT, @EmployeeIdXml
SELECT EmployeeID, LastName, FirstName, Title FROM Employees
WHERE EmployeeID IN
      (
            SELECT EmployeeID
            FROM OPENXML(@Xdoc, '/Employees/Employee', 1)
            WITH(EmployeeId int '@EmployeeId')
      )

EmployeeID  LastName             FirstName  Title


1           Davolio              Nancy      Sales Representative

4           Peacock              Margaret   Sales Representative

5           Buchanan             Steven     Sales Manager

6           Suyama               Michael    Sales Representative

 

Hope this helps someone.

Happy coding,

Tom Hundley
Elegant Software Solutions

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s