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!