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