Solved

SET CONCAT_NULL_YIELDS_NULL  alternatives?

Posted on 2008-10-24
3
620 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

705 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