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
