Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
azharrahi
Starting Member
44 Posts |
 Posted - 2006-09-02 : 01:31:30
|
| hi all people here I want to write a stored procedure which updates a table.the table has columns C1,C2,C3,C4,C5,... and input parameters are @C1,@C2, @C3,@C4,@C5,... respectively. Now the problem is that: If @C2 parameter, and only it, contains an empty value(or null value), then the column C2 must not be updated while other columns must be updated. One way is that I must write queries with different columns with in same stored procedure based on "If-Condition", missing THAT ONE COLUMN Having Empty parameter.e.g If @C2 = null Begin Update Table Set C3 = @C3,C4 = @C4,C5 = @C5 where C1 = @C1End If @C3 = null Begin Update Table Set C2 = @C2,C4 = @C4,C5 = @C5 where C1 = @C1End ............ (and so on)This approach might be acceptable for very limited columns .. But it becomes too much long and contains too much "If-Conditions" for a larger number of columns (upto 20 columns or more). Can anyone one tell me the best approach to handle this problem.i.e only one query must update the columns of tables based on "Empty-parameter(s)", leaving that column(s) (having empty parameter) without updation, while others must be.More than one parameter can be null and hence THAT Columns must be left without updation.Azhar RahiSoftware EngineerEye4tech Pvt Ltd,Lahore Pakistan |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-09-02 : 04:10:11
|
you can use isnull or coalesceupdate table set C1 = isnull(@C1, C1), C2 = isnull(@C2, C2), C3 = isnull(@C3, C3), C4 = isnull(@C4, C4), C5 = isnull(@C5, C5)
KH |
 |
|
|
|
|
|
|
|
How to block updation one column while others must