This may be common knowledge to those among us that are handy with XML (I'm not), but since I just uncovered this nice little trick I thought I'd pass it along either way. It is a very easy way to process a CSV string and turn it into multiple rows:
declare @idoc int -- the handle for the XML documentdeclare @csv varchar(8000) -- the CSV we are being passed
set @csv = '1,2,3,7,4,8,10' -- our list of CSV values
-- With a simple edit of the CSV string:
set @csv = ''
-- We can now do this:
EXEC sp_xml_preparedocument @idoc OUTPUT, @csv
-- And then:
SELECT *FROM ...