CODEWITHSUNDEEP
stored-procedures
Lonzo
Lonzo

Reputation: 2818

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

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

Upvotes: 1

Views: 224

Answers (6)

ChrisLively
ChrisLively

Reputation: 88072

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. MS SQL 2008 has table valued parameters which allow you to pass in the data in a table format.

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 or table value parameters. 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.

Upvotes: 2

Manrico Corazzi
Manrico Corazzi

Reputation: 11371

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.

Upvotes: 0

Aaron Palmer
Aaron Palmer

Reputation: 9002

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.

Upvotes: 0

kemiller2002
kemiller2002

Reputation: 115508

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>

Upvotes: 0

John Boker
John Boker

Reputation: 83719

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.

Upvotes: 0

John Saunders
John Saunders

Reputation: 161791

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

Upvotes: 2

Related Questions