Passing comma separated values for IN list in DELETE statement from SQLCMD
-
Hello,
Following are the contents of script Delete_employees.sql.
USE CompanyDB
BEGIN TRAN
DELETE FROM dbo.employee
WHERE empid IN (<Comma separated values passed from SQLCMD>)
COMMIT TRAN;
Question:
Will I be able to pass comma separate values from SQLCMD for the IN Clause in DELETE statement?
I tried running the above but it does seem to work:
sqlcmd -S localhost\testEXPRESS -i Delete_employees.sql -v empid=100
When I change the IN clause to = in delete sql statement within Delete_employees.sql script, the above SQLCMD works.
But I want to be able to pass comma separate employee ids (which is a INT) for the IN clause in DELETE statement.
Can somebody help me figure out a way to do that?
Thanks!
-
I would recommend approaching this differently. IN ( @variable ) is notoriously a pita to get to work.
Instead, use a string splitter (delimited8k, SQLCLR, whatever you like) and turn your string ('10,11,12,18,42') into a joined iTVF for the procedure. It'll take from 1 through x parameters that way and you'll avoid a lot of the headaches.
That's the incredibly short version of the task, and it'll get a lot more detailed if you're not familiar with that. Let us know if that doesn't get you on the right track and either I or someone else will spell it out when we get more time. If you need more assistance if you can post the source Proc that will help us integrate what you'll need.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Thanks!
Can you please give an example of how to do it?
I'm quite new to sql server and would really appreciate the help.
Thanks
-
Alright, finally home and can spend a little time on this. First, get your hands on a copy of the function DelimitedSplit8k from here: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Read the article as well, please. You'll have to get acquainted with the Tally Table as well. These can be some more advanced topics but the short version is if you copy/paste the code and feed it a string with comma delimited values it'll hand you back a 'table' of those values as a single column. My usual disclaimer here: If you don't understand the code you're putting in production, DON'T.
Anyways, that said, once you get a handle on it, you'll usually have a proc that looks a little like this before you start and it won't work because IN won't take your variable:
CREATE PROC GetData@SplitmeString VARCHAR(8000)
AS
SELECT
st.*
FROM
SomeTable AS st
WHERE
st.ID IN (@SplitmeString)
GO
Call would be something like:
EXEC GetData @SplitmeString = '1,2,4,8,16,32,64'
So, with our new handy-dandy function, now our proc will look like this:
CREATE PROC GetData
@SplitmeString VARCHAR(8000)
AS
SELECT
st.*
FROM
SomeTable AS st
JOIN
DelimitedSplit8K( @SplitmeString, ',') AS dsk
ONst.ID = dsk.Item
GO
So, what have we really done? Basic SQL: An INNER JOIN restricts row data coming from both sides via the ON clause. Because your list (even if it only had one element) is now restricting to only the IDs you want, the JOIN takes the place of the WHERE. The function returns a table from your string, and thus will allow you to use it that way.
This gets around the entire problem of trying to dynamically construct code to allow your delimited string to be included in the query.
- Craig FarrellNever stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA -
Thanks.
Got the below Table-Valued Function logic from:http://www.sommarskog.se/arrays-in-sql-2005.html#CSV (Click on Erland's CLR version under CLR) via the link you provided http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx
Table-Valued Function that unpacks the string into a table:
CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END
The function iterates over the string looking for commas, and extracts the values one by one. The code is straightforward, and makes use of some of the string functions in <small>T-SQL</small>. The most complex part is the <small>CASE</small> expression which exists to handle the last value in the string correctly. This example shows how you could use this function:
CREATE PROCEDURE get_product_names_iter @ids varchar(50) AS
SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
JOIN iter$simple_intlist_to_tbl(@ids) i ON P.ProductID = i.number
go
EXEC get_product_names_iter '9, 12, 27, 37'
The article does mention that the above function above is not extremely speedy.
Though my database is in SQL SERVER 2008, I used the above Table-Values Function approach over Table-valued Parameter logic mentioned in http://www.sommarskog.se/arrays-in-sql-2008.html#TVP_in_TSQL since in my usecase there wont be more than 30 to 35 items in comma separated list. Moreover, with the above non-TVP approach, I dont have to create a new TYPE object.
Please let me know if the choice is inefficent for my usecase.
Thanks!
-
anonymous2009 (9/9/2012)
Thanks.Got the below Table-Valued Function logic from:http://www.sommarskog.se/arrays-in-sql-2005.html#CSV (Click on Erland's CLR version under CLR) via the link you provided http://sqlblog.com/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx
Table-Valued Function that unpacks the string into a table:
CREATE FUNCTION iter$simple_intlist_to_tbl (@list nvarchar(MAX))
RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
DECLARE @pos int,
@nextpos int,
@valuelen int
SELECT @pos = 0, @nextpos = 1
WHILE @nextpos > 0
BEGIN
SELECT @nextpos = charindex(',', @list, @pos + 1)
SELECT @valuelen = CASE WHEN @nextpos > 0
THEN @nextpos
ELSE len(@list) + 1
END - @pos - 1
INSERT @tbl (number)
VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
SELECT @pos = @nextpos
END
RETURN
END
The function iterates over the string looking for commas, and extracts the values one by one. The code is straightforward, and makes use of some of the string functions in <small>T-SQL</small>. The most complex part is the <small>CASE</small> expression which exists to handle the last value in the string correctly. This example shows how you could use this function:
CREATE PROCEDURE get_product_names_iter @ids varchar(50) AS
SELECT P.ProductName, P.ProductID
FROM Northwind..Products P
JOIN iter$simple_intlist_to_tbl(@ids) i ON P.ProductID = i.number
go
EXEC get_product_names_iter '9, 12, 27, 37'
The article does mention that the above function above is not extremely speedy.
Though my database is in SQL SERVER 2008, I used the above Table-Values Function approach over Table-valued Parameter logic mentioned in http://www.sommarskog.se/arrays-in-sql-2008.html#TVP_in_TSQL since in my usecase there wont be more than 30 to 35 items in comma separated list. Moreover, with the above non-TVP approach, I dont have to create a new TYPE object.
Please let me know if the choice is inefficent for my usecase.
Thanks!
Consider NOT using a WHILE loop and scalar UDF for doing splits because both make code unnecessarily slow. Please see the article at the link that "Evil Kraig F" provided for a much more effecient splitter.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
CELKO (9/11/2012)
I have a two articles on this topic at Simple Talk. There is no need to throw away performance, maintainabilty and portability to do this.http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/
http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/
You do know that if you put the IFCode shortcuts [ url ] and [ /url ] (no spaces inside the square brackets) around your urls above that people could actually click on them and go directly to those articles without having to waste their time doing a cut and paste.
-
CELKO (9/11/2012)
I have a two articles on this topic at Simple Talk. There is no need to throw away performance, maintainabilty and portability to do this.http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/
http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/
If portability to another DBMS is a concern then the methods Joe discusses are a good solution.
On the other hand, if you don't see yourself switching to another DBMS in the near future and you want to keep your maintenance a lot simpler then you might be better off following the ideas that Craig has suggested. For me, I am a sql server guy and I don't really worry about portability. I also don't want to have to alter my procedure and redeploy my data access tier every time I need to make the list longer.
_______________________________________________________________
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Sean Lange (9/11/2012)
CELKO (9/11/2012)
I have a two articles on this topic at Simple Talk. There is no need to throw away performance, maintainabilty and portability to do this.http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists/
http://www.simple-talk.com/sql/learn-sql-server/values()-and-long-parameter-lists---part-ii/
If portability to another DBMS is a concern then the methods Joe discusses are a good solution.
On the other hand, if you don't see yourself switching to another DBMS in the near future and you want to keep your maintenance a lot simpler then you might be better off following the ideas that Craig has suggested. For me, I am a sql server guy and I don't really worry about portability. I also don't want to have to alter my procedure and redeploy my data access tier every time I need to make the list longer.
+1000^1000!
Use the capabilities of the system you are using. How often do you see companies changing database systems? It isn't something done lightly or on a frequent basis.
-
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
-
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
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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. 😀
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)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply