A NULL Question with "+=" Syntax in SQL 2008

During an internal presentation today, a co-worker asked a question about working with nulls using the new T-SQL syntax of “+=”

I tried the following code and it seems to work as expected.  Feel free to give it a run.

 

--Requires a SQL 2000 for validation.  Code Works of course.

 

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';

GO

-- SET CONCAT_NULL_YIELDS_NULL ON and testing.

SET CONCAT_NULL_YIELDS_NULL ON;

GO

DECLARE @NullValue varchar(10)

 

DECLARE @Alpha varchar(10)

SET @Alpha = 'abc'

 

SET @Alpha = @Alpha + @NullValue ;

SELECT @Alpha;

GO

 

-- SET CONCAT_NULL_YIELDS_NULL OFF and testing.

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

DECLARE @NullValue varchar(10)

 

DECLARE @Alpha varchar(10)

SET @Alpha = 'abc'

 

SET @Alpha = @Alpha + @NullValue ;

SELECT @Alpha;

GO

 

 

--- Requires SQL 2008 - Will not work on 2000.

 

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';

GO

-- SET CONCAT_NULL_YIELDS_NULL ON and testing.

SET CONCAT_NULL_YIELDS_NULL ON;

GO

DECLARE @NullValue varchar(10);

 

DECLARE @Alpha varchar(10) = 'abc'

 

SET @Alpha += @NullValue ;

SELECT @Alpha;

GO

 

-- SET CONCAT_NULL_YIELDS_NULL OFF and testing.

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

DECLARE @NullValue varchar(10)

 

DECLARE @Alpha varchar(10)= 'abc'

 

SET @Alpha += @NullValue ;

SELECT @Alpha;

 

 

--Flip It

 

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';

GO

-- SET CONCAT_NULL_YIELDS_NULL ON and testing.

SET CONCAT_NULL_YIELDS_NULL ON;

GO

DECLARE @NullValue varchar(10);

 

DECLARE @Alpha varchar(10) = 'abc'

 

SET  @NullValue += @Alpha ;

SELECT @NullValue;

GO

 

-- SET CONCAT_NULL_YIELDS_NULL OFF and testing.

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

DECLARE @NullValue varchar(10)

 

DECLARE @Alpha varchar(10)= 'abc'

 

SET  @NullValue += @Alpha ;

SELECT @NullValue;

Published Friday, April 25, 2008 12:01 PM by Keith Nicholson

Comments

# re: A NULL Question with "+=" Syntax in SQL 2008

Friday, April 25, 2008 12:38 PM by Rob

You should post the results for those that don't have the CTP available.

Leave a Comment

(required) 
(required) 
(optional)
(required) 


Copyright © is the original authors. Blog site is an independent site not sponsored by Microsoft. The Yoda blog server and the Brianna SQL server would like to thank www.ownwebnow.com and www.exchangedefender.com. They wouldn't be here and broadcasting without the generosity of Vlad Mazek and his companies.

Powered by Community Server (Commercial Edition), by Telligent Systems