CONCAT_NULL_YIELDS_NULL

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

declare @strNull nvarchar(50) = null

SET CONCAT_NULL_YIELDS_NULL ON

select (@strNull + 'aaa') as str_result -- result: NULL

SET CONCAT_NULL_YIELDS_NULL OFF

select (@strNull + 'aaa') as str_result -- result: 'aaa'

http://msdn.microsoft.com/en-us/library/aa259202(v=sql.80).aspx