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
anonymous2009
anonymous2009
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 175
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!
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8577 Visits: 7660
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 Farrell

Never 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
anonymous2009
anonymous2009
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 175
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
Evil Kraig F
Evil Kraig F
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8577 Visits: 7660
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
ON st.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 Farrell

Never 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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
anonymous2009
anonymous2009
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 Visits: 175
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!
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85575 Visits: 41082
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.
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
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

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

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

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

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)
Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

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

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
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