June 30, 2006 at 2:26 pm
I have a web page with a form that updates the database upon submit. I want to change this to update upon change of each field. So that whenever the field is updated, it executes the update. Would it make sense to create a stored proc that receives two variables, one for the target field and one for the value, and then use dynamic sql to perform the update?
IE:
Declare @field varchar(20)
Declare @value varchar(50)
@sql = "
Update Table1
Set " + @field + " = " + @value
I know sometimes dynamic sql is not advisable and I'm trying to build the best configuration for this kind of update. This table is limited to just a couple dozen rows so it is very small. The interface is also limited to just a few users.
Thanks!
June 30, 2006 at 3:06 pm
Create a SP that accepts parameters of all columns for the table with a default of Null.
Update TableA SET col1 = COALESCE(@col1, col1) , col2 = COALESCE(@col2, col2), .....
where @col1, @col2 are the parameter that are passed to the SP.
From the web page, call the SP with the value of the updated field and other columns with Null values.
June 30, 2006 at 4:38 pm
Get suggestion, thanks!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply