SQL Server - Concatenating string / integer with NULL

In SQL Server , SET statement - CONCAT_NULL_YIELDS_NULL -
Controls whether concatenation results are treated as null or empty string values.

Concatenation of string with NULL (Example) :

DECLARE @a VARCHAR(5) = 100
SET CONCAT_NULL_YIELDS_NULL ON;
SELECT @a + NULL; 
SET CONCAT_NULL_YIELDS_NULL OFF;
SELECT @a + NULL; 

Concatenation of integer with NULL (Example) :

In case of  integer , we cannot make use of CONCAT_NULL_YIELDS_NULL ,
CONCAT_NULL_YIELDS_NULL works only with string types .

SET CONCAT_NULL_YIELDS_NULL OFF;
DECLARE @b INT = 100
SELECT @b + NULL ;
SELECT @b + ISNULL(NULL,0) ; 

In SQL Server 2012 , we have CONCAT( ) function to concatenate string with NULL .

For example :

SELECT CONCAT(100,NULL)

--returns 100


Reference - http://technet.microsoft.com/en-us/library/ms176056.aspx

See Also:

•  Posts on T-SQL scripts