TSQL String Concatenation with NULL and CONCAT Function
String Concatenation with plus (+) operator Example: the result of this query will return NULL because @mname is NULL. Note: NULL mark represent missing data. Thus known data + missing data = unknown DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20); SELECT @lname + ', ' + @fname + ' ' + @mname AS fullName; Example: this query result an error because can't convert 'x, y' string to an TINYINT. DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20), @a_number TINYINT = 2; SELECT@lname + ', ' + @fname + ' ' + @a_number AS fullName_and_number; Result: Conversion failed when converting the varchar value 'y, x ' to data type tinyint. To avoid the error above we can convert number 2 to a string like this: DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20), @a_number TINYINT ...