SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
LANdango.com
LANdango.com
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 2580
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 / 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


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26311 Visits: 17553
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 / 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





You should read the article referenced repeatedly. In case you missed it you can find it in my signature about splitting strings. The XML type of splitter you posted is not only discussed but performance tested in that article. It is way faster than this type of XML splitting.


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.


This is EXACTLY what the Delimited Splitter does. Returns the data from a table valued function. The scenario you describe is the entire reason it was created. There is nothing inherently wrong with the XML splitter but the other version will beat on performance. :-)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87152 Visits: 41113
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.


The performance of the DELETE is really nice. If you have a lot of "hits" on a proc that passes data in a CSV format, then you should probably reconsider the splitter method for performance purposes (unless you actually pass XML). XML splitters of this nature are nearly as slow as a While Loop.



The little black line near the bottom is the new DelimitedSplit8K function.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
LANdango.com
LANdango.com
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 2580
Sean/Jeff:
Thank you. I read the articles AFTER I posed. Unsure

I ran some tests and I'm amazed by DelimitedSplit8K's speed. I ran tests using both methods on the following string -- just random jibberish:
'1,2,4,5,6,7,8,99,10,2,4,5,6,7,8,99,10,2,4,5,6,7,8,99,10,2,4,5,6,7,8,99,10,2,4,5,6,7,8,99,10'

XML : 55 seconds
DS8K : 43 seconds (28% faster!)

Great work!

I guess the good news here is that if I need to screaming performance, use DS8K. :-D
Good news for my project is that the DBAs understand the XML version; but I'm going to mention DS8K for a project that really needs to scream.

Thank you for all your great work and time! (I enjoy seeing the objective analysis you two have put together on this topic. I just wish Microsoft would address this issue. :-D)

(ah man, i really thought i was submitting a good nugget! i felt like i was hording it for years lol)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search