Updating multiple rows

[manjulam]

I have multiple rows to be updated on click of a submit button in the page. What is the best way to update multiple records in sql server. Do i need to use cursor? Can i pass arrays of each column to the stored procedure?

 

[hmckillop]

Different ways to do this:
If you are talking lots of rows I would do this via XML, create a XML response string, pass it to the stored proc and then use on UPDATE statement joining on the XML doc to update the data.
Alternatively if its only a few records of a determinate nature, i would call the proc several times.
Avoid using cursors where possible.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[SQLSister]

I would suggest that it is a bad idea to ever allow a user to do a multiple record insert or update for data integrity reasons. Why? Suppose you are Joe Blow user and you have a list of 100 records on your screen and you change 57 of them and click the submit button. This all works fine as long as in the two hours it took you to do this, you don't lose power or accidentally close the application, etc. But users do thoses things and then they have to redo 57 changes. Worse, the user probably doesn't know which things were in that batch and need to be fixed or what he originally changed the data to, hence the data integrity problems. And what happens if he did something wrong on the 33rd change. When it runs in a batch, then the entire thing might fail. Users get cranky when this happens even though it was their own fault for putting in bad data.

There is no array data type for stored procedures to use. You can use a large varchar field and then parse the data into temp variable or temp tables in the procedure itself unless the data strings would be too long. Then use the temp table in the update statement. This may or may not be faster than using a cursor, you would have to experiment and try both ways.

But personally I would tell whoever thinks this is a good requirement that iti swill make the system more user error prone and less reliable and slower and then ask him to allow you do things differently. Part of a dba's job is to tell management when they want something that is inefficient and why another way would be better.

Questions about posting. See faq183-874

 

[hmckillop]

SQLSister,

I understand what you are saying but for example, we have multiple permissions per account (an on/off state), and it wouldnt be feasible to have these updated on a row by row basis, so we have a screen which presents them in a series of pages, with checkboxes.
On completion of their updates to the UI, they click save, we then persist the changes to the DB via an XML file and a single Update statement (obviously within a transaction).
We have a highly concurrent system and havent noticed any significant problems (as yet).
Any other suggestions for something like this?


"I'm living so far beyond my income that we may almost be said to be living apart

 

[ESquared]

Passing a list of values to a stored procedure part 2

-------------------------------------
A sacrifice is harder when no one knows you've made it.