>>>>>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
>

Hi Frank:

Your SQL is erroneous, because ISNULL() only accepts 1 parameters and returns True/False, so in this case Thierry was right in using NVL(), but at the cost of always replacing the value (with the actual one or the new one, whatever)

But if you are processing to much data and want the best performance, then Dragan's suggestion of building your UPDATE (with a series of IF..ENDIF for each field) is the best performing and does not add any noticeable cost of maintenance.