Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Passing comma separated values for IN list in DELETE statement from SQLCMD Expand / Collapse
Author
Message
Posted Friday, November 9, 2012 8:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:59 AM
Points: 5, Visits: 2,580
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

Post #1383048
Posted Friday, November 9, 2012 9:50 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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



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 Moden's 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)
Post #1383106
Posted Friday, November 9, 2012 11:42 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1383175
Posted Friday, November 9, 2012 12:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 19, 2014 11:59 AM
Points: 5, Visits: 2,580
Sean/Jeff:
Thank you. I read the articles AFTER I posed.

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.
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. )

(ah man, i really thought i was submitting a good nugget! i felt like i was hording it for years lol)
Post #1383200
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse