UPDATE statement not consistent when including NULL fields So I want to append Field B to C, then copy field A to B and set field A to "Awaiting update".

I have written the following code

UPDATE Notes

SET

/* append previous month to notes */
Notes=Notes + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) + '---' + CAST(GETDATE() as         nvarchar(max)) + CHAR(13)+CHAR(10) + cast(LastMonth as nvarchar(max))

/* replace previous month with current month */
, LastMonth=CurrentNote

/* replace previous month with current month*/
, CurrentNote=cast('To be updated' as nvarchar(max))


The problem i've found is that if any of the fields in the row are blank the update statement no longer works as expected.

What is the correct method for going about an update like this?
In SQL Server, when you concatenate string, if one part is NULL the result of the string will be NULL. To avoid this behavior, you have to test all your string with the ISNULL function to ensure a string empty instead of the NULL value.

UPDATE Notes

SET

/* append previous month to notes */
Notes=ISNULL(Notes,'') + CHAR(13)+CHAR(10) + CHAR(13)+CHAR(10) + '---' + CAST(GETDATE() as         nvarchar(max)) + CHAR(13)+CHAR(10) + cast(ISNULL(oppo_rota_last_progress_update,'') as nvarchar(max))

/* replace previous month with current month */
, LastMonth=CurrentNote

/* replace previous month with current month*/
, CurrentNote=cast('To be updated' as nvarchar(max))