Strange syntax in an Update Statement-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

This is basically stating: If the zip column does not equal zip, then the new value should be @zip (the variable) otherwise the new value should be the existing value.

Your method is not making a comparison and just setting it to the variable.

Both should output the same result, unless the variable is null. This will cause a short circuit and the IF will be FALSE, and the current value will be retained.

DECLARE @table TABLE (zip NVARCHAR(10))
INSERT INTO @table (zip) VALUES ('90210-1234')

DECLARE @zip NVARCHAR(10)

SELECT * 
  FROM @table

UPDATE @table
   SET zip = IIF(@zip<>zip,@zip,zip)

SELECT * 
  FROM @table

UPDATE @table
   SET zip = @zip

SELECT * 
  FROM @table

SET @zip = '90210-1234'

UPDATE @table
   SET zip = @zip

SELECT * 
  FROM @table

zip
---
90210-1234  --Original Value

zip
---
90210-1234  --Not updated because of short circuit

zip
---
NULL        --Set to NULL as the variable was NULL

zip
---
90210-1234  --Set to a value