Another scenario I forgot to mention is when the parameter being passed is a GUID. Since guids are by definition a fixed length when part of a string consisting of an array of guids, there is an even more efficient parsing method credited by Sommarskog to Ken Henderson. Here is my implementation of the guid parser using nvarchar(MAX) to allow a basically unlimited number of GUID's:

CREATE FUNCTION dbo.fnParseGUIDs(@str nvarchar(MAX))
RETURNS @ParsedValues TABLE (pk int, GUID uniqueidentifier)
AS

BEGIN
INSERT INTO @ParsedValues (pk, GUID)
SELECT Number
, CAST(LEFT(SUBSTRING(@str, 37 * (Number - 1) + 1, 37),36) AS uniqueidentifier)
FROM Numbers
WHERE Number <= DATALENGTH(@str) / (37 * 2)
+ CASE DATALENGTH(@str) % 37 WHEN 0 THEN 0 ELSE 1 END
RETURN
END

Happy Holidays,

Tim

timoverlund@hotmail.com 12/23/2005 12:56:58 PM

We have definitely run into this issue on our current project. However, I don't believe the UDF presented is particularly efficient as it basically uses a WHILE loop to do cursor-like row-based processing of the array. Here is an alternative I borrowed from Erland Sommarskog's excellent article 'Arrays and Lists in SQL Server' (http://www.sommarskog.se/arrays-in-sql.html#overview). It uses a prepopulated Numbers table which is simply an integer column with values from 1-8000. In order to get a key for ordering the list, I originally added an IDENTITY column, which I replaced with the SQL Server 2005 ROW_NUMBER() OVER syntax.

CREATE FUNCTION dbo.fnParseDelimitedList (@list varchar(7998), @delimiter char(1))
RETURNS @ParsedValues TABLE (pk int, Value varchar(7997))
AS

BEGIN
INSERT INTO @ParsedValues (pk, Value)
SELECT ROW_NUMBER() OVER(ORDER BY Number), SUBSTRING(@delimiter + @list + @delimiter, Number + 1,
CHARINDEX(@delimiter, @delimiter + @list + @delimiter, Number + 1) - Number - 1)
AS Value
FROM dbo.Numbers
WHERE Number <= LEN(@delimiter + @list + @delimiter) - 1
AND SUBSTRING(@delimiter + @list + @delimiter, Number, 1) = @delimiter
RETURN
END

Tim Overlund, MCDBA, MCSD.NET
Database Practice
Avanade, Inc

timoverlund@hotmail.com 12/23/2005 12:37:55 PM