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

A different approach: the null is tested at the VFP side, reducing the need for redundant updates (in your statement, every column is always updated, no matter what).

	TEXT TO m.lcSQL NOSHOW TEXTMERGE
		UPDATE Employees 
		SET <<IIF(ISNULL(m.emp_firstname),"","emp_firstname = ?m.emp_firstname")>>
			<<IIF(ISNULL(m.emp_middlename),"","emp_middlename = ?m.emp_middlename")>>
			<<IIF(ISNULL(m.emp_lastname),"","emp_lastname = ?m.emp_lastname")>>
			<<IIF(ISNULL(m.emp_dob),"","emp_dob = ?m.emp_dob")>>
			<<IIF(ISNULL(m.emp_clifk),"","emp_clifk = ?m.emp_clifk")>>
			<<IIF(ISNULL(m.emp_number),"","emp_number = ?m.emp_number")>>
			<<IIF(ISNULL(m.emp_relfk),"","emp_relfk = ?m.emp_relfk")>>
			<<IIF(ISNULL(m.emp_empfk),"","emp_empfk = ?m.emp_empfk")>>
			<<IIF(ISNULL(m.emp_sexfk),"","emp_sexfk = ?m.emp_sexfk")>>
			<<IIF(ISNULL(m.emp_transfer),"","emp_transfer = ?m.emp_transfer")>>
			<<IIF(ISNULL(m.emp_email),"","emp_email = ?m.emp_email")>>
			<<IIF(ISNULL(m.emp_title),"","emp_title = ?m.emp_title")>>
			<<IIF(ISNULL(m.emp_pensionpolicy),"","emp_pensionpolicy = ?m.emp_pensionpolicy,")>>
			<<IIF(ISNULL(m.emp_BIR),"","emp_BIR = ?m.emp_BIR")>>
			<<IIF(ISNULL(m.emp_NIS),"","emp_NIS = ?m.emp_NIS")>>
			<<IIF(ISNULL(m.emp_height),"","emp_height = ?m.emp_height")>>
			<<IIF(ISNULL(m.emp_weight),"","emp_weight = ?m.emp_weight")>>
			<<IIF(ISNULL(m.emp_phone),"","emp_phone = ?m.emp_phone")>>
			<<IIF(ISNULL(m.emp_employdate),"","emp_employdate = ?m.emp_employdate")>>
			<<IIF(ISNULL(m.emp_occupation),"","emp_occupation = ?m.emp_occupation")>>
			<<IIF(ISNULL(m.emp_maritalstatus),"","emp_maritalstatus = ?m.emp_maritalstatus")>>
			<<IIF(ISNULL(m.emp_address1),"","emp_address1 = ?m.emp_address1")>>
			<<IIF(ISNULL(m.emp_address2),"","emp_address2 = ?m.emp_address2")>>
			<<IIF(ISNULL(m.emp_address3),"emp_address3 = ?m.emp_address3")>>
		WHERE emp_pk = ?m.emp_pk
	ENDTEXT
	
	IF "SET ," $ m.lcSQL
		m.lcSQL = STRTRAN(m.lcSQL,"SET ),"",""SET ")
		ExecuteQuery(m.lcSQL)
	ENDIF

(hope that I got it right, having no sample data to test...).

Edit: the final test must have some tuning, because of newlines and tabs, but the general idea seems ok...