USE CompanyDB

BEGIN TRAN

DELETE FROM dbo.employee
WHERE empid IN (<Comma separated values passed from SQLCMD>)

COMMIT TRAN;

sqlcmd -S localhost\testEXPRESS -i Delete_employees.sql -v empid=100

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

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'

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:



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:



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!

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/

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/

Sean Lange (9/11/2012)

---

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.