Need example of Conditional update stored proc in SQL server

I am just at starting levels in DB usage and have 2 basic questions

1. I have a generic UPDATE stored proc which updates all columns of a table.

But i need to make it conditional wherein it does not SET when the parameter is NULL.

Usage: I want to use this as a single SP to UPDATE any subset of columns, the caller from C# will fill in corresponding parameter values and leave other parameters NULL.

2

In case of , "U开发者_如何学运维PDATE selected records" do i need to use locking inside stored proc ?

Why ? Isn't the operation in itself locked and transactional ?

I find the same question come up when i need to UPDATE selected(condition) records and then Return updated records.

UPDATE table SET a = case when @a is null then a else @a end WHERE id = @id

OR

EXEC 'update table set ' + @update + ' where id = ' + @id

OR

Conditionally update a column at a time

First option to me would usually be preferrable as it is usually efficient enough and you do not need to worry about string escaping

If I have understood the question properly, Why can't you build a query on the fly from sql server SP, and use sp_sqlexecute. So when you build query you can ensure only columns that have value has got updated.

Does this answer your question?