If you're using SQL Server 2005, then this is a simple matter with the
XML datatype. Look for video called XML and MultiSelect listbox on my
web site (http://www.ifoundtime.com/community)

You' need to use the nodes method to shread the XML parameter into a
relation of XML nodes. You can then use the value method on the node
to get the specific data. Once you have the parameter as a relation
use it as a sub-query for the IN clause of your main query.

For example.

declare @cities xml
set @cities = '<keys><key id="Baltimore"/><key id="Albany" /><key
id="Oakland"/></keys>'
select * from person.address where city in (
select c.value('@id','varchar(30)') from @cities.nodes('//key') as
t(c) )

as an example that works against the adventureworks samaple database
of SQL Server 2005.

If you're using SQL Server 2000 then you'll have to use the OPENXML
rowset function to accomplish roughly the same thing, although there
is more coding overhead.

Cheers,
Ralph Shillington
iFoundTime Inc.
http://www.ifoundtime.com/community





[quoted text, click to view]

Hi all,

How do I pass in variable for an IN clause?

For example:

SELECT * FROM TABLE WHERE Column IN (1,2,3,4,5)

How do I pass the 1,2,3,4,5 as a SQL Parameter?

Can I go: SELECT * FROM TABLE WHERE Column IN (@MyParameter)?