PMH4514
asked on
SET CONCAT_NULL_YIELDS_NULL alternatives?
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."
http://msdn.microsoft.com/en-us/library/ms176056.aspx
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?
thanks
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."
http://msdn.microsoft.com/en-us/library/ms176056.aspx
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?
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Do you want the ENTIRE ending string to be '' if any component are NULL, or the null component to be treated as '' and the rest of it would be built?
ASKER
Brandon - I want the null component treated as '', the rest of built up string kept in tact.
angellll's solution does the trick.. ISNULL method..
man I have a lot to learn about TSQL!
angellll's solution does the trick.. ISNULL method..
man I have a lot to learn about TSQL!