quote:

---

Firstly, the problem with passing comma separated varchars is that you're limited in length to what you can pass. You can get round this using SQL Server 2000's XML features to pass _unlimited_ length arrays.

Here's how to do it for VB->ADO->SQL Stored Proc:

1. Assuming you have a (string) array of longs in VB use a function to build a simple xml-based listing of your items:

Function ArrayToXML(arrFrom() As String) As String
Dim i As Integer
Dim strTemp As String
Dim strElement As String

strTemp = "<A>"
For i = 0 To UBound(arrFrom)
strElement = arrFrom(i)
strTemp = strTemp & "<E I=""" & strElement & """/>"
Next
strTemp = strTemp & "</A>"
ArrayToXML = strTemp
End Function

2. Now pass that into your stored proc using 'text' datatype:
.Parameters.Append .CreateParameter("@XMLArray", adVarChar, adParamInput, 2147483647, ArrayToXML(arrMyIDs))

3. Declare xml doc in your stored procedure:

DECLARE @XMLArrayDoc int
EXEC sp_xml_preparedocument @XMLArrayDoc output, @XMLArray

4. Return the values using FOR OPENXML:

SELECT I As myData FROM OPENXML (@XMLArrayDoc, '/A/E/@I', 1) WITH (I int '.')

You can put these values into a temp table, a cursor or simply use it in an 'IN'. Works with multi-dimensional arrays too.

5. Don't forget to kill the xml doc at the end of your stored proc

EXEC sp_xml_removedocument @XMLArrayDoc

---