Hello Ned,
Thanks to SQL Server's XML support, you can dispense with the
delimited-string idea and proceed using XML. Build a mini XML document
for your data, for example:
<root>
<item name="itemname" value="itemvalue"/>
<item name="itemname2" value="itemvalue2"/>
</root>
Then, send this string as varchar input to your SP. Using the built in
SP sp_xml_PrepareDocument (see Books Online) to load your xml. Then
read the values out using OPENXML (see Books Online).
Your XML data is "shredded" by OPENXML into a table, whence you can
perform an update using all the input data. In the example above, each
item tag would shredded into a row, each attribute name (name, value),
becomes a column, and the values of the attributes are the field values
for the columns.
Ned Balzer wrote:
Hi,
Can anyone tell me what is the best approach for passing multiple keys
from a listbox to a SQL update? I prefer to use stored procedures for
updates. I am using asp.net 2.0 and SQL 2000, soon to upgrade to SQL
2005.
I can envision several ways to do it, such as passing a delimited set
of keys in a string to the sp, which then parses through them and maybe
puts them into a temp table and performs the update. or is there maybe
a cooler way to do it, like passing some custom array object to the sp,
or even somehow creating the temp table in the asp.net code and passing
that object as a param to the sp?
I would prefer to avoid using any iterative approach on the SQL side,
if that's possible.
Thanks.
-- Ned