?
Solved

Passing comma separated values for IN list from SQLCMD

Posted on 2012-09-04
5
Medium Priority
?
717 Views
Last Modified: 2012-11-15
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;

Open in new window


Question:
Is it possible 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

Open in new window


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!
0
Comment
Question by:sath350163
  • 3
5 Comments
 
LVL 18

Accepted Solution

by:
deighton earned 750 total points
ID: 38366984
you can build a sql string and execute it using EXECUTE

http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure

just make sure the string you build is literally what you want to execute

---------------------------------------------------------

the other tougher way is to parser the list of id's using SQL string functions, drop the values in a temp table or table variable, then
delete from X where ID in (select id from temptable)

have done that, would need to look out code, the 'build a string' method is fastest to develop
0
 
LVL 19

Assisted Solution

by:Rimvis
Rimvis earned 750 total points
ID: 38367717
Hi sath350163,

You can use XML to quickly parse delimited string:

DECLARE @emplist VARCHAR(500)
SET @emplist = '1,2,3'

DECLARE @xml XML
SET @xml = cast(('<X>'+replace(@emplist,',' ,'</X><X>')+'</X>') as xml)

DELETE dbo.employee WHERE empid In (SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N))

Open in new window

0
 

Author Comment

by:sath350163
ID: 38381556
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

Open in new window


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'

Open in new window


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!
0
 

Author Comment

by:sath350163
ID: 38604651
I've requested that this question be closed as follows:

Accepted answer: 0 points for sath350163's comment #a38381556
Assisted answer: 250 points for deighton's comment #a38366984
Assisted answer: 250 points for Rimvis's comment #a38367717

for the following reason:

Solution worked for my use case
0
 

Author Comment

by:sath350163
ID: 38604652
Thanks!
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question