Transact-SQL: Control whether concatenation results are treated as nullor empty string values

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, '')