Wednesday, September 21, 2011

Controls the concatenation results with NULL are treated as NULL or empty string values with SET CONCAT_NULL_YIELDS_NULL

When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'Bunty' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'Bunty' + NULL yields Bunty.

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';
GO

SET CONCAT_NULL_YIELDS_NULL ON;
GO
SELECT 'Bunty' + NULL ;
GO

SET CONCAT_NULL_YIELDS_NULL OFF;
GO
SELECT 'Bunty' + NULL;
GO

Below TSQL returns the current setting value for CONCAT_NULL_YIELDS_NULL

SELECT   SESSIONPROPERTY ('CONCAT_NULL_YIELDS_NULL')
 

No comments:

Post a Comment