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! :w00t:

Here's a working sample:

Create proc DeleteStories(@CommaSeparatedListOfIds NVARCHAR(MAX))

AS

BEGIN

/*11/09/2012::Created by M.M.(www.reinid.com / http://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

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.