Ecatenate client case studies

T-SQL Tips & Tricks

Passing a CSV list or Array Stored Procedure

"How do I pass a delimited list, CSV, or array of ID's into a stored procedure?"

A common approach is to use a comma separated varchar and pass this to the stored procedure as a parameter. The problem with passing a varchar is that you're limited in length to what you can pass - a maximum of 8000 characters.

You can get round this using one of SQL Server 2000's XML features to pass arrays of unlimited length.

Here's how to do it from Visual Basic:

1. Create a function to convert your array of strings into an xml list of items:

Function ArrayToXML(arrList() As String) As String
Dim i As Integer

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

You can convert a delimited list into an array using the Join function.

2. Next pass the xml list into your stored procedure using the 'text' datatype:

VB6
Dim oCommand As ADODB.Command
Set oCommand = New ADODB.Command

With oCommand
	Set .ActiveConnection = myConnection
	.CommandType = adCmdStoredProc
	.CommandText = "spDeleteCustomers"
	.Parameters.Append .CreateParameter("@XMLArray", _ 
		adVarChar, _ 
		adParamInput, _ 
		2147483647, _ 
		ArrayToXML(arrCustomerIDList))
	'other parameters...
	.Execute , , adExecuteNoRecords
End With

VB.NET
Dim oSqlCommand As New SqlCommand

With oSqlCommand
    .CommandText = "spDeleteCustomers"
    .Parameters.Add(New SqlParameter("@XMLArray", SqlDbType.Text)).Value = ArrayToXML(arrCustomerIDList)
    'other parameters...
End With

3. Next, in your stored procedure declare an xml document and extract your list into a table variable. You can then use the table for INSERT, UPDATE or DELETE's:

CREATE PROCEDURE spDeleteCustomers
	@XMLArray text
AS

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

DECLARE @temp TABLE (CustomerID int)

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

DELETE tblCustomer
FROM tblCustomer A
INNER JOIN @temp B ON A.CustomerID = B.CustomerID

EXEC sp_xml_removedocument @XMLArrayDoc

GO

You can put the list into a temp table, table variable, a cursor or simply use it with the 'IN' operator. With some simple modifications you can also use this approach for multi-dimensional arrays.