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 = 2;

SELECT @lname + ', ' + @fname + ' ' + CAST(@a_number AS VARCHAR(5)) AS fullName_and_number;

String Concatenation with CONCAT Function

Available on SQL Server 2012

Concatenate input parameters to a string

All parameters are implicitly converted to string

NULL parameter are implicitly convert to empty string

Require minimum 2 parameters

Example:

DECLARE @lname VARCHAR(20) = 'x', @fname VARCHAR(20) = 'y', @mname VARCHAR(20), @a_number TINYINT = 2;
SELECT CONCAT(@lname, ', ', @fname, ' ', @mname, @a_number) AS fullName_and_number;

BOL CONCAT Function: http://technet.microsoft.com/en-us/library/hh231515.aspx