sath350163
asked on
Passing comma separated values for IN list from SQLCMD
Hello,
Following are the contents of script Delete_employees.sql.
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:
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!
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:
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
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!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Thanks!
ASKER
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:
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:
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!