Thanks everyone for all the suggestions. The udf worked
great!
--
Bryan Ashcraft (remove brain to reply)
Web Application Developer
Wright Medical Technologies, Inc.
=============================
Macromedia Certified Dreamweaver Developer
Adobe Community Expert (DW) ::
http://www.adobe.com/communities/experts/
"Stefan K." <webforumsuser@macromedia.com> wrote in
message
news:e4eldj$9mv$1@forums.macromedia.com...
> ScareCrow is correct.
> Passing a list as IN-parameter to a stored procedure is
bound to fail.
> SQL simply treats a list as a string, by no means a a
list of
> numeric-values
> or list of several strings.
>
> The simple workaround would be to write a normal query,
don't use stored
> proc
> in this case.
> The other way is using one of the following SQL-udf,
they take such a
> string-list and returns a temp-table(position, value).
> There's a version for working with string-lists and one
for numeric-lists.
>
>
> Example:
> SELECT TOP 1 *
> FROM dbo.udf_iter_stringlist_to_table(
'firstItem,secondItem', ',')
> Would return:
> listpos | string
> 1 | 'firstItem'
>
> Numeric-list UDF:
> CREATE FUNCTION dbo.udf_iter_intlist_to_table (@list
ntext, @delim nchar)
> RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> number int NOT NULL) AS
> BEGIN
> DECLARE @pos int,
> @textpos int,
> @chunklen smallint,
> @str nvarchar(4000),
> @tmpstr nvarchar(4000),
> @leftover nvarchar(4000)
>
> SET @textpos = 1
> SET @leftover = ''
> WHILE @textpos <= datalength(@list) / 2
> BEGIN
> SET @chunklen = 4000 - datalength(@leftover) / 2
> SET @tmpstr = ltrim(@leftover + substring(@list,
@textpos,
> @chunklen))
> SET @textpos = @textpos + @chunklen
>
> SET @pos = charindex(@delim, @tmpstr)
> WHILE @pos > 0
> BEGIN
> SET @str = substring(@tmpstr, 1, @pos - 1)
> INSERT @tbl (number) VALUES(convert(int, @str))
> SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1,
len(@tmpstr)))
> SET @pos = charindex(@delim, @tmpstr)
> END
>
> SET @leftover = @tmpstr
> END
>
> IF ltrim(rtrim(@leftover)) <> ''
> INSERT @tbl (number) VALUES(convert(int, @leftover))
>
> RETURN
> END
>
> String-list-UDF
> CREATE FUNCTION dbo.udf_iter_stringlist_to_table (@list
ntext, @delim
> nchar)
> RETURNS @tbl TABLE (listpos int IDENTITY(1, 1) NOT NULL,
> string varchar(8000) COLLATE database_default NOT
> NULL) AS
> BEGIN
> DECLARE @pos int,
> @textpos int,
> @chunklen smallint,
> @str nvarchar(4000),
> @tmpstr nvarchar(4000),
> @leftover nvarchar(4000)
>
> SET @textpos = 1
> SET @leftover = ''
> WHILE @textpos <= datalength(@list) / 2
> BEGIN
> SET @chunklen = 4000 - datalength(@leftover) / 2
> SET @tmpstr = ltrim(@leftover + substring(@list,
@textpos,
> @chunklen))
> SET @textpos = @textpos + @chunklen
>
> SET @pos = charindex(@delim, @tmpstr)
> WHILE @pos > 0
> BEGIN
> SET @str = substring(@tmpstr, 1, @pos - 1)
> INSERT @tbl (string) VALUES(@str)
> SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1,
len(@tmpstr)))
> SET @pos = charindex(@delim, @tmpstr)
> END
>
> SET @leftover = @tmpstr
> END
>
> IF ltrim(rtrim(@leftover)) <> ''
> INSERT @tbl (string) VALUES(@leftover)
>
> RETURN
> END
>