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
Tom Hundley
Elegant Software Solutions, LLC
Subscribe