>>>>I think I should be able to use IIF() to update only when the parameter passed in is not null.
>>>
>>>You can build the update statement based on the new values, and simply not include those which are null or empty.
>>
>>Thanks, but that's what I wanted to avoid. Using one statement with the IIF() does exactly what I want.
>
>The example statement had no ";" at the end of line, so I assumed this was what you were sending to the server.
>
>One iif() per field? The amount of checking is pretty much the same, just fewer lines.
This just seems to me to be cleaner and more maintainable:
TEXT TO m.lcSQL NOSHOW TEXTMERGE
UPDATE Employees
SET emp_firstname = ISNULL(?m.emp_firstname, emp_firstname)
,emp_middlename = ISNULL(?m.emp_middlename, emp_middlename)
,emp_lastname = ISNULL(?m.emp_lastname, emp_lastname)
,emp_dob = ISNULL(?m.emp_dob, emp_dob)
,emp_clifk = ISNULL(?m.emp_clifk, emp_clifk)
,emp_number = ISNULL(?m.emp_number, emp_number)
,emp_relfk = ISNULL(?m.emp_relfk, emp_relfk)
,emp_empfk = ISNULL(?m.emp_empfk, emp_empfk)
,emp_sexfk = ISNULL(?m.emp_sexfk, emp_sexfk)
,emp_transfer = ISNULL(?m.emp_transfer, emp_transfer)
,emp_email = ISNULL(?m.emp_email, emp_email)
,emp_title = ISNULL(?m.emp_title, emp_title)
,emp_pensionpolicy = ISNULL(?m.emp_pensionpolicy, emp_pensionpolicy)
,emp_BIR = ISNULL(?m.emp_BIR, emp_BIR)
,emp_NIS = ISNULL(?m.emp_NIS, emp_NIS)
,emp_height = ISNULL(?m.emp_height, emp_height)
,emp_weight = ISNULL(?m.emp_weight, emp_weight)
,emp_phone = ISNULL(?m.emp_phone, emp_phone)
,emp_employdate = ISNULL(?m.emp_employdate, emp_employdate)
,emp_occupation = ISNULL(?m.emp_occupation, emp_occupation)
,emp_maritalstatus = ISNULL(?m.emp_maritalstatus, emp_maritalstatus)
,emp_address1 = ISNULL(?m.emp_address1, emp_address1)
,emp_address2 = ISNULL(?m.emp_address2, emp_address2)
,emp_address3 = ISNULL(?m.emp_address3 , emp_address3)
WHERE emp_pk = ?m.emp_pk
ENDTEXT