In the past we would use a Varchar to pass a comma delimited list of IDs to a stored proc. We would then build a big varchar string of the full SQL and run the dynamic SQL. Starting with SQL Server 2005 you can pass the stored proc XML and do your select without using dynamic SQL.
The stored proc would look something like:
create proc [dbo].[getLocationTypes](@locationIds XML,
@typeIds XML=null)
as
begin
set nocount on
SELECT locationId, typeId
FROM xrefLocationTypes
WHERE locationId
IN (SELECT Item.value('.', 'int' )
FROM @locationIDs.nodes('IDList/ID') AS x(Item))
AND (typeId IN
(SELECT Item.value('.', 'int' )
FROM @typeIds.nodes('IDList/ID') AS x(Item)))
ORDER BY 1, 2
end
And then you would call this like:
EXECUTE dbo.getLocationTypes '<IDList><ID>1</ID><ID>3</ID></IDList>',
'<IDList><ID>200</ID><ID>300</ID><ID>400</ID></IDList>'