Best way to send an array of values to a stored proc

Question

Should I use a CSV string, an XML string or...?

Answer 1

Which database and version? With SQL Server 2008, you can use table-valued parameters. On 2005, not so much.

Answer 2

I usually use a CSV string so i don't go over the max length for a varchar field. XML seems kind of wordy for that purpose.

Answer 3

There are specific API for doing that... In Java you can prepare statements with objects and specify the database types, JDBC will handle the conversions. This is pretty vendor-specific, anyway. I would not recommend using either of the suggested techniques because they "hide" the type of the arguments with dangerous side effects.

Answer 4

In 2005/2000 we had to create an Xml string and either parse it using the Xml Parser (2005) or roll our on in 2000. Not great, but it got the job done.

  <ArrayOfGuid><Guid></Guid></ArrayOfGuid>

Answer 5

It really depends on which database you are using.

MS SQL 2005 introduced the XML data type to the Microsoft platform. Oracle9i brought in the XMLTYPE. MySql also has XML functions such as ExtractValue and UpdateXML.

The question really boils down to how you want to use the values. Are you ripping them apart to store in a table or do you want to store the whole blob?

If ripping apart AND there are only a couple values, I'd recommend going with regular string parsing. Otherwise do XML.

Bear in mind that SQL (regardless of flavor) is not really geared towards string parsing. So, you might create a helper function (CLR, UDF, etc) within the database to handle it for you if you go that route.

Answer 6

Whenever I have to send an array to a sproc I generally take a step back and consider "why?". Often I am able to send some other criteria to the sproc that would allow the sproc to generate the correct results without the need for an array.

There are cases when I have ended up having to pass the array, and in those cases I have found that passing xml works fine in mssql.