Solved

SET CONCAT_NULL_YIELDS_NULL  alternatives?

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

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now