Concatenation of strings with a null value will result in NULL.
For example:
DECLARE @NullString VARCHAR
SELECT @NullString = NULL
SELECT 'MyString' + @NullString
Will return NULL and not ‘MyString.
Until now we could use
SET CONCAT_NULL_YIELDS_NULL OFF
When concatenating a null value with a string yields the string itself (the null value is treated as an empty string).
Microsoft has issued the following warning:
In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
And this leaves us with the ISNULL solution:
DECLARE @NullString VARCHAR
SELECT @NullString = NULL
SELECT 'MyString' + ISNULL(@NullString, '')
