The table has a trigger to log any changes.
Now I have an asp.net update page for updating the above person table

The question is, if user just want to update address='apple street' , the above update statement will update all the fields but not check if the original value = new value, then ignore this field and then check the next field. So my log table will log all the event even the columns are not going to be updated.

At this point, my solutions

Select all the value by id and store them into local variables.
Using if-else check and generate the update statement. At last,
dynamically run the generated SQL (sp_executesql)

Select all the value by id and store them into local variables.
Using if-else check and update each field seperately:

If @dob <> @ori_dob
Begin
Update person set date_of_birth=@dob where id=@id
End

May be this is a stupid question but please advice me if you have better idea, thanks!

Is your log table populated using a trigger on person? If so you might want to consider checking each column there instead of in the update procedure.
–
pilotcamJul 17 '12 at 1:54

What's the issue if the user is only updating one of the columns? Why not let him update everything and if one of the columns being updated already has the same value; let it be updated as well.
–
IcarusJul 17 '12 at 1:54

@pilotcam I see. This is another solution for me, thanks.
–
Steve LamJul 17 '12 at 2:20

@user1439709 why don't you have an Audit table with the exact same structure as the source table and insert a row per/update instead of a row per column updated? Unless you have a valid business reason to do this, let me tell you that your approach is rather unorthodox and smells bad to me.
–
IcarusJul 17 '12 at 2:56