Solved

SET CONCAT_NULL_YIELDS_NULL  alternatives?

Posted on 2008-10-24
3
617 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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

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…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

730 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