Post reply

Making many calls into one

  • August 25, 2008 at 8:55 am

    #189743

    I wrote a program with a [ComponentArt] grid

    one piece of functionality is to select a group of items and to change all the prices for the selected items

    I implemented it by iterating through the selected items and issuing update calls for each

    there has to be a faster way

    can I create an XML file of the keys to the selected items, the new price and write a stored procedure to join the XML (as a temporary table, perhaps?) to update the records

    looking for best practices here

    thanks

    Marianne

  • August 25, 2008 at 9:44 am

    #862603

    Actually what you've considered doing with passing XML to the database stored procedure is a very common practice. You can use the XML capabilities of SQL Server 2005 to easily convert the XML data back into a table variable or directly into a join in your UPDATE statement. For example, if you have an XML parameter called [font="Courier New"]@PriceChangeXML[/font] with a root node, and each "item" being an element with ItemID and Price attributes:

    [font="Courier New"]SELECT

    list.rows.value('@ItemID', 'int'),

    list.rows.value('@Price', 'decimal(9,2)')

    FROM @PriceChangeXML.nodes('Root/Item') AS list(rows)[/font]

  • August 25, 2008 at 12:45 pm

    #862721

    thanks!

    actually, all I need to put into XML are the keys of the records to be changed

    the price will be the same for all so it can be a separate parameter

  • August 27, 2008 at 1:02 pm

    #863933

    worked like a charm

    took a bit of futzing to get Element with Attribute nodes in VB.NET

    but it turned out to be very simple code

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply