You can control the concatenation results using SQL ServerCONCAT_NULL_YIELDS_NULL(T-SQL). Depending upon the CONCAT_NULL_YIELDS_NULL settings, the concatenation results are treated as null or empty string values. When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. When SET CONCAT_NULL_YIELDS_NULL is OFF then the null value is treated as an empty string. Concatenating a null value with a string yields the string itself.

For Example:

 SET CONCAT_NULL_YIELDS_NULL ONdeclare @firstname varchar(15)declare @lastname varchar(15)select @firstname='Bush'select @lastname=nullselect @[email protected]

Result:

 NULLSET CONCAT_NULL_YIELDS_NULL OFFdeclare @firstname varchar(15)declare @lastname varchar(15)select @firstname='Bush'select @lastname=nullselect @[email protected]

Result:

 Bush