sukhendass (7/8/2010)
SET CONCAT_NULL_YIELDS_NULL ONSELECT ISNULL('abcd'+NULL+1,'1234567890')
if i add any numeric value then it gives->1234567890 why?
The explanation posted by Arto is almost correct, but not entirely.
The inner expression ('abcd'+NULL+1) is evaluated left to right. The first part is 'abcd'+NULL. The 'abcd' is character (varchar(4) to be precise); the NULL is untyped, but based on context SQL Server concludes that you probably meant character as well, and uses the minimum length (varchar(1)). The result is varchar(5); because of the CONCAT_NULL_YIELDS_NULL, the value is NULL.
The second part is ( ('abcd'+NULL) + 1). The 'abcd' + NULL is, as we have seen, varchar(5); the 1 is considered to be integer. Rules of data type precedence say that in this case, varchar(5) gets converted to int - so NULL typed as varchar(5) gets converted to NULL typed as int, 1 is added, and the result is still NULL (and still typed as int)
Now, for a mental detour - what happens if you change SET CONCAT_NULL_YIELDS_NULL to OFF? In that case, the evaluation remains the same, but the result of 'abcd' + NULL is now 'abcd' (again, tpyed as varchar(5)). The + 1 again introduces conversion to integer - but for 'abcd', this conversion fails, so now you get a runtime error.