So... We have an .NET/ASP app that we are developing and I am looking for some advice on the most efficient approach for Updates.
Below are the methods being considered, along with identifying the current method used and I want to make sure the approach is sound. Any advice is appreciated, thanks in advance. Our goal for the app is that faster is always better for these operations.
Option 1 - Current Method:
Code the update SP's to accept a parameter for every update-able field in the table, each parameter has a default value of NULL. The actual UPDATE statement is hard coded to update every field in the table each time and each value is wrapped with a COALESCE() to flush out the values that have actually been passed to the SP. This results in every field being updated every time with either a new value or itself.
Dynamic SQL within the SP to determine which parameters in the SP actually have been passed values and dynamically create and execute the UPDATE statement to only update the necessary fields.
Dynamic SQL passed from the application/business layer to the SQL Server. (We already have everything done with SP's so this is not a desirable method for us)
Write many update statements for each field in the table and call them one at a time when necessary ... (yuck)