Generic stored procedure to update a field value. 1

[diana123]

Hi, I'm an MSAccess developer who is about to start using SQL Server. I have done numerous searchs and haven't found what im after.
Im just wondering if it is possible to have a generic stored procedure that would accept 5 parameters to update a particular field value, in a particular table for a particular record
eg pass in the tablename, primarykey field name, primarykey field value, field to be updated name and field value.
My idea was to use an access database as a front end and use a pass through query to update the field value. Just wondering if this can be done, or do i have to look at using specific procedures for each field or even ado to update just one field in a record.
I would prefer to update on a field level and not update the entire record each time as some of the tables have a large number of fields and we can have situations where many users are editing the same record at the one time.

Any advice / recommendations would be greatly appreciated, and please go easy as im a newby when it comes to SQL server.
Diana

 

[mrdenny]

This can be done, but it's not recommended.

You would have to use Dynamic SQL to do this, and dynamic SQL has security and performance issues which go along with using it.

There are tons of examples on dynamic SQL in this forum. If you can't find any give a shout and I'll post a couple of quick ones. (It's late and I'm feeling lazy today.)

You will have much better performance within SQL Server by updating the entire row at the same time. If you update each field seperatly and say 10 fields need to be updated, that's 10 seperate updates which SQL has to process, instead of a single update. The more work which SQL Server can to per command the better performance you will get from SQL Server.

If you have the potential for users editing the same record and the same time, setup a field on the record which tracks who's working on it. As soon as the record is opened has the field be updated with the users info. When they leave the record null out the field. When the record is loaded if the field is not null make the screen read only. If the field is null make it read/write. You can also include a datetime field which is set when ever the person starts using the record so they can only lock the record for a specific amount of time. If that amount of time has passed and another user tries to edit the record they can be given the option of stealing the record from the prior user.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog