CREATE TABLE stringarray (spid int NOT NULL, listpos int NOT NULL, str varchar(4000) NOT NULL, nstr nvarchar(2000) NOT NULL, CONSTRAINT pk_array PRIMARY KEY (spid, listpos)) go CREATE PROCEDURE charlist_to_table_spid @list ntext, @delimiter char(1) = N',' AS DECLARE @pos int, @textpos int, @chunklen smallint, @listpos int, @tmpstr nvarchar(4000), @leftover nvarchar(4000), @tmpval nvarchar(4000), @sql nvarchar(4000) SET NOCOUNT ON DELETE stringarray WHERE spid = @@spid SELECT @textpos = 1, @leftover = '', @listpos = 1 WHILE @textpos <= datalength(@list) / 2 BEGIN SELECT @chunklen = 4000 - datalength(@leftover) / 2 SELECT @tmpstr = @leftover + substring(@list, @textpos, @chunklen) SELECT @textpos = @textpos + @chunklen SELECT @pos = charindex(@delimiter, @tmpstr) WHILE @pos > 0 BEGIN SELECT @tmpval = left(@tmpstr, @pos - 1) SELECT @tmpval = ltrim(rtrim(@tmpval)) INSERT stringarray(spid, str, nstr, listpos) VALUES (@@spid, @tmpval, @tmpval, @listpos) SELECT @listpos = @listpos + 1 SELECT @tmpstr = substring(@tmpstr, @pos + 1, len(@tmpstr)) SELECT @pos = charindex(@delimiter, @tmpstr) END SELECT @leftover = @tmpstr END INSERT stringarray(spid, str, nstr, listpos) VALUES(@@spid, ltrim(rtrim(@leftover)), ltrim(rtrim(@leftover)), @listpos)