Controls the concatenation results with NULL are treated as NULL or empty string values with SET CONCAT_NULL_YIELDS_NULL

When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'Bunty' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'Bunty' + NULL yields Bunty.

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';

GO

SET CONCAT_NULL_YIELDS_NULL ON;

GO

SELECT 'Bunty' + NULL ;

GO

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

SELECT 'Bunty' + NULL;

GO

Below TSQL returns the current setting value for CONCAT_NULL_YIELDS_NULL

SELECT   SESSIONPROPERTY ('CONCAT_NULL_YIELDS_NULL')