Solved

SET CONCAT_NULL_YIELDS_NULL  alternatives?

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Pivot Query Problem 9 41
CREATE DATABASE ENCRYPTION KEY 1 63
Strange msg in the SSMS pane 13 55
SQL Query with Sum and Detail rows 2 49
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
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.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

776 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