Solved

SET CONCAT_NULL_YIELDS_NULL  alternatives?

Posted on 2008-10-24
3
612 Views
Last Modified: 2012-05-05
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


0
Comment
Question by:PMH4514
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 22796618
this will work:
declare @result nvarchar(2000)
declare @val nvarchar(1000) 
select @val= val from inserted 
select @result = 'test' + isnull(''','''+ @val,'')

Open in new window

0
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 22796729
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?
0
 

Author Comment

by:PMH4514
ID: 22796782
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!  
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
OPENDATASOURCE 8 42
Not selecting duplicate data 6 60
How to simplify my SQL statement? 14 55
Please help for the below sql query. 1 28
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question