SQLTeam.com | Weblogs | Forums

How to update all fields EXCEPT incoming null data?

tsql

#1

I need to setup an SP to update a record in my table, however any incoming data my SP uses to make the update that is NULL, should be ignored. What's the best way to go about doing this?

So just to clarify with an example, I have a table called Table1 with one record (sorry if my formatting comes out terrible):
Table1_Id | Field1 | Field 2 | Field3

123 | qrs | tuv | wxyz

I'm passing these values into my SP:
ID | Value1 | Value 2 | Value3

123 | abc | NULL | def

Since Value2 is NULL, Table1.Field2's new value should stay the same. Here's my new table:
Table1_Id | Field1 | Field 2 | Field3

123 | abc | tuv | def

What's the best way to handle this?

Thanks


#2
update table1
   set field1=isnull(@value1,field1)
      ,field2=isnull(@value2,field2)
      ,field3=isnull(@value3,field3)
 where table1_id=@id
;

#3

Thanks bitsmed. That's perfect!