This whole morning just spent about 1 hours+ just to try out one very simpel query on MS SQL 2005.
I keeping get the null value whenever i run this query, even in my program or run directly from the MS SQL 2005 management studio.
Finally I found out that is you are concatenate null values, either the concat null yields null setting of sp_dboption or SET CONCAT_NULL_YIELDS_NULL determines the behavior when one expression is NULL.
With either concat null yields null or SET CONCAT_NULL_YIELDS_NULL enabled ON, String + NULL returns NULL. If either concat null yields null or SET CONCAT_NULL_YIELDS_NULL is disabled, the result is String?
This is the case 1
Update tableA
set field1 + ‘My value’
where field2 = 1
This is the case 2
select ‘test ‘ + null + ‘ string’
returns
————
NULL
This is the solution
set concat_null_yields_null off
select ‘test ‘ + null + ‘ string’
returns
————
test string
More From david_cheong
david_cheong Recommends
- Is working out a good way to meet people of the opposite sex? | Sportize.me (Sportize.me)
- Making Money With Cell Phones, My First Month Running A Mobile Site (SiteBuilderTips.com)
Popularity: 1% [?]
