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 Tuesday, September 4, 2012 4:46 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 2:13 AM
Points: 34, 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!


Post #1354247
Posted Tuesday, September 4, 2012 5:07 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 6,147, Visits: 7,200
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
Post #1354255
Posted Tuesday, September 4, 2012 5:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 2:13 AM
Points: 34, 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
Post #1354260
Posted Tuesday, September 4, 2012 8:08 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:56 PM
Points: 6,147, Visits: 7,200
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
Post #1354300
Posted Sunday, September 9, 2012 6:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 2:13 AM
Points: 34, 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!






Post #1356529
Posted Sunday, September 9, 2012 10:08 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 12:18 AM
Points: 36,751, Visits: 31,202
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."

(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 #1356549
Posted Tuesday, September 11, 2012 7:28 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, July 20, 2014 11:55 AM
Points: 1,945, Visits: 2,860
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/


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1357394
Posted Tuesday, September 11, 2012 8:08 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:42 PM
Points: 23,009, Visits: 31,510
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.



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)
Post #1357441
Posted Tuesday, September 11, 2012 8:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:21 PM
Points: 13,083, Visits: 11,918
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 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 #1357443
Posted Tuesday, September 11, 2012 8:11 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 8:42 PM
Points: 23,009, Visits: 31,510
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.



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)
Post #1357447
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse