In the query below, if both conditions are true then the last entry is updated in ErrorMessage. If one record has country more than 3 characters and length of HR_DOMAIN_Code is not equal to 3, then ErrorMessage should look like 'Invalid Country' & 'Invalid HR_DOMAIN_CODE':
set ErrorMessage =
case when len(Country) > 3 then 'Invalid Country'
when len(HR_DOMAIN_CODE) != 3 then 'Invalid HR_DOMAIN_CODE'
You can just add another
WHEN to handle both conditions:
UPDATE A SET ErrorMessage = CASE WHEN LEN(Country) > 3 AND LEN(HR_DOMAIN_CODE) != 3 THEN 'Invalid Country & Invalid HR_DOMAIN_CODE' WHEN LEN(Country) > 3 THEN 'Invalid Country' WHEN LEN(HR_DOMAIN_CODE) != 3 THEN 'Invalid HR_DOMAIN_CODE' ELSE NULL END
However, the above will be very complicated once you have multiple combinations to check. My suggestion is to use a separate
CASE expression of each of the condition. Something like this:
UPDATE A SET Errormessage = STUFF(( SELECT CASE WHEN LEN(Country) > 3 THEN '& Invalid Country' ELSE '' END + CASE WHEN LEN(HR_DOMAIN_CODE) != 3 THEN '& Invalid HR_DOMAIN_CODE' ELSE '' END ), 1, 2, '')