> You can't send arrays into a stored procedure in this way (at least MSDE
> 2000 you can't).  I'm not sure about 2005, but I don't think this facility
> has been added, at least not with Vanilla SQL.  What I do is construct an
> NTEXT field and then parse it into a temporary table in the stored
> procedure, then do a join with that temporary table.  Code below.  Remember
> that your NTEXT items are space separated.  You can change to comma or
> Umlaught (!) separated or whatever you want.  If your have a small maximum
> number of items likely to be selected, you could I suppose have a different
> parameter for each one.  But for the general case this is the only way I
> know to do it:
>
>
>
>
> ' Collect the items into NTEXT format ("12034 23245 5435 5353", etc. )
>
> Dim theBuilder As New StringBuilder
>
> For i As Integer = 0 To Items.Count - 1
>
>     theBuilder.Append(Items(i).ToString)
>     theBuilder.Append(" ")
>
> Next
>
> ' Remove trailing space.
>
> theBuilder.Remove(theBuilder.Length - 1, 1)
>
> ....
>
> ' Send the NTEXT field into the stored procedure......
>
> Command.Parameters.Add("@In_Items", SqlDbType.NText).Value =
> theBuilder.ToString()
> Command.Parameters("@In_Items").Direction = ParameterDirection.Input
>
> ......
>
>
>
>
> ' Here is how you join in the stored procedure:
>
> SELECT * FROM MyTable INNER JOIN dbo.Split ( @In_Items ) i ON MyTable.Value
> = i.value
>
>
>
>
> .............
>
>
>
> ' and here is a stored procedure - modify to suit.  Mine splits an NTEXT of
> space separated integers into ID's, so I can join on that field.  Yours
> might be text values, I don't know :).....
>
>
> CREATE FUNCTION dbo.Split (
>
>         @list NTEXT
> )
>
> RETURNS @Table 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(' ', @tmpstr)
>
>             WHILE @pos > 0
>             BEGIN
>
>                 SET @str = substring(@tmpstr, 1, @pos - 1)
>
>
>                 INSERT  @Table (number)
>                         VALUES
>                         (convert(int, @str))
>
>                 SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1,
> len(@tmpstr)))
>
>
>                 SET @pos = charindex(' ', @tmpstr)
>             END
>
>             SET @leftover = @tmpstr
>
>         END
>
>         IF ltrim(rtrim(@leftover)) <> ''
>
>             INSERT  @Table (number)
>              VALUES
>   (convert(int, @leftover))
>
>         RETURN
>
>
>
>
>     END