SET CONCAT_NULL_YIELDS_NULL (T-SQL)

Controls whether or not concatenation results are treated as null or empty string values.

Syntax

SET CONCAT_NULL_YIELDS_NULL {ON | OFF}

Remarks

When SET CONCAT_NULL_YIELDS_NULL  is ON, concatenating a null value with a string yields a NULL result. For example, SELECT ‘abc’ + 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 ‘abc’ + NULL yields abc.

If not specified, the setting of the concat null yields null database option applies.

Note SET CONCAT_NULL_YIELDS_NULL is the same setting as the concat null yields null setting of sp_dboption.

The setting of SET CONCAT_NULL_YIELDS_NULL  is set at execute or run time and not at parse time.

See Also