csv-sql-arg-alternative.sql

--table with some test data
select * into #mytable from (
    select 1 [id],'one' [value] union all
    select 2,'two' union all
    select 3,'three' union all
    select 4,'four' union all
    select 5,'five') st

--bad way passing raw csv string data in
declare @csv varchar(50)
set @csv = '1,3,5'
declare @sql nvarchar(2000)
set @sql = 'select * from #mytable where id in (' + @csv + ')'
exec sp_executesql @sql

--vs

--xml document with root, using attributes
declare @xml as xml
set @xml = '<root><v id="1" /><v id="3" /><v id="5" /></root>'
select
    t.*
from
    #mytable t join @xml.nodes('//v') x(n)
    on n.value('@id','int') = t.id

--simple collection of xlm nodes
declare @xml2 as xml
set @xml2 = '<v>1</v><v>3</v><v>5</v>'
select
    t.*
from
    #mytable t join @xml2.nodes('/v') x(n)
    on n.value('.','int') = t.id

drop table #mytable