Passing comma separated values for IN list in DELETE statement from SQLCMD

declare @xml xml
set @xml = N'<root><r>' + replace(replace(@CommaSeparatedListOfIds,',','</r><r>'),' ', '') + '</r></root>'

Delete from MyTable
where ID IN (
select r.value('.','nvarchar(max)') as item
from @xml.nodes('//root/r') as records(r))

Create proc DeleteStories(@CommaSeparatedListOfIds NVARCHAR(MAX))
AS
BEGIN
/*11/09/2012::Created by M.M.(www.reinid.com / www.nmtcr.com)
Purpose: Deletes a records by rows
Sample execution:
exec DeleteStories '1,2,3,4,10,100,1000,1001,2001'
*/

/*Next section is not really needed if the input is clean
set @CommaSeparatedListOfIds = replace(@CommaSeparatedListOfIds, ',,',',') --just in case doubles come in

--Ensure that there are no trailing commas in input
while(right(@CommaSeparatedListOfIds,1) = ',')
begin
set @CommaSeparatedListOfIds = LEFT(@CommaSeparatedListOfIds,len(@CommaSeparatedListOfIds)-1)
end
*/

--Here's the real "magic"

--Create an XML document that will be used to extract the IDs from the string input
declare @xml xml
set @xml = N'<root><r>' + replace(replace(@CommaSeparatedListOfIds,',','</r><r>'),' ', '') + '</r></root>'

--Display the values
select r.value('.','nvarchar(max)') as item from @xml.nodes('//root/r') as records(r)

--Or use the r.value in an IN statement
/*
Delete from StoriesTable where StoryId IN (select r.value('.','nvarchar(max)') as item from @xml.nodes('//root/r') as records(r))
*/

END

LANdango.com (11/9/2012)

The anwers above are pretty good; however, there's a short cut. use XML. In just a few lines you can get what you want and the performance is really nice. Using this approach eliminates the need for round-trips to the server. (Think how fast this would be by flagging data grid rows, collecting the IDs into a string and then calling a procedure one time to delete all the selected records in just one call! Heck, you could return the table of ids from a table valued function. Have fun with it!

declare @xml xml
    set @xml = N'<root><r>' + replace(replace(@CommaSeparatedListOfIds,',','</r><r>'),' ', '') + '</r></root>'

Delete from MyTable 
    where ID IN (
         select r.value('.','nvarchar(max)') as item 
         from @xml.nodes('//root/r') as records(r))

DONE!

Here's a working sample:

Create proc DeleteStories(@CommaSeparatedListOfIds NVARCHAR(MAX))
AS
BEGIN
   /*11/09/2012::Created by M.M.(www.reinid.com / www.nmtcr.com)
      Purpose:  Deletes a records by rows
      Sample execution:
      exec DeleteStories '1,2,3,4,10,100,1000,1001,2001'
  */
	
  /*Next section is not really needed if the input is clean
	set @CommaSeparatedListOfIds = replace(@CommaSeparatedListOfIds, ',,',',') --just in case doubles come in

	--Ensure that there are no trailing commas in input
	while(right(@CommaSeparatedListOfIds,1) = ',')
	  begin
	  set @CommaSeparatedListOfIds = LEFT(@CommaSeparatedListOfIds,len(@CommaSeparatedListOfIds)-1)
	end
	*/
  
  --Here's the real "magic"

    --Create an XML document that will be used to extract the IDs from the string input 
    declare @xml xml
    set @xml = N'<root><r>' + replace(replace(@CommaSeparatedListOfIds,',','</r><r>'),' ', '') + '</r></root>'
   
	--Display the values
	select r.value('.','nvarchar(max)') as item from @xml.nodes('//root/r') as records(r)

   --Or use the r.value in an IN statement
   /*
	Delete from StoriesTable where StoryId IN (select r.value('.','nvarchar(max)') as item from @xml.nodes('//root/r') as records(r))
   */

END