I was looking through the MSDN because I was having problems building concatenated strings when some of the variables used to build the string were NULL. I wanted the result to be '' rather than the entire string NULL.
I found the SET CONCAT_NULL_YIELDS_NULL OFF option, which solves my problem, only, the following MSDN article states "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."
But it does not give me any insight into an alternative solution that will not fail in future releases.
I'm using this in a trigger, where I declare variables o typef nvarchar for fields in the inserted record, and I do for example:
declare @result nvarchar(2000)
declare @val nvarchar(1000)
select @val=val from inserted
select @result = 'test' + ''','''+ @val
when @val is NULL, I"d like @result just to be 'test', which is what happens when I set CONCAT_NULL_YIELDS_NULL to OFF. When I don't make that setting, @result ends up NULL.
What is the proper solution to work with future releases?