The following code is intended to update an emp record. However, if Zip is null, the data does not get updated.
I did not write this code, and I am not sure of the original intention behind using this type of syntax and I don't see a benefit to it.
Example: zip = iif(zip<>@zip,@zip,zip)
I would just write this: zip = @zip
Am I missing anything?
The intention is to update the zip field as long as the @zip parameter is not null. The problem is that it does not get updated with the zip field currently is null.
UPDATE emp
SET first_name = iif(first_name <> @first_name,@first_name,first_name)
,last_name = iif(last_name <> @last_name, @last_name, last_name)
,dob = iif(dob <> @dob,@dob,dob)
,social_security_num = iif(social_security_num<>@social_security_num,@social_security_num,social_security_num)
,dl_num = iif(dl_num<>@dl_num,@dl_num,dl_num)
,dl_state = iif(dl_state<>@dl_state,@dl_state,dl_state)
,gender = iif(gender<>@gender,@gender,gender)
,address1 = iif(address1<>@address1,@address1,address1)
,address2 = iif(address2<>@address2,@address2,address2)
,city = iif(city<>@city,@city,city)
,zip = iif(zip<>@zip,@zip,zip)
,STATE = iif(state<>@state,@state,state)
,primary_phone = iif(primary_phone<>@primary_phone,@primary_phone,primary_phone)
,emergency_contact = iif(emergency_contact<>@emergency_contact,@emergency_contact,emergency_contact)
,secondary_phone = iif(secondary_phone<>@secondary_phone,@secondary_phone,secondary_phone)
,emergency_contact_phone = iif(emergency_contact_phone<>@emergency_contact_phone,@emergency_contact_phone,emergency_contact_phone)
,emp_pay_type_id = @emp_pay_type_id
WHERE emp_id = @emp_id
There is a difference: The column will never be updated to null.
Any comparison with null is not true (except for the special IS NULL expression), even if compared to another null.
The expression
iif(zip<>@zip,@zip,zip)
has the same effect as
case when @zip is null or zip is null then zip else @zip end
Note also that if the column is itself null, it will also never be updated.