The operator to concatenate in SQL Server is '+', but this operator is also used to add values.

Being the same operator for both, the query analyzer makes a concatenation or a sum based on the type of data fields being treated. If the fields are of 'string' type, the operator concatenate, and if it has numeric fields, two integers, for example, it add the two values.

We have the problem when we have a field of each type and the operator '+' between. In this case, the engine may return a conversion error because it finds a numeric field trying to execute the sum and, of course, the other field is a string. 

For example, we have these fields in a table EmployeesTable: 
 strName -> nvarchar (10) 
 intAge -> integer 

If we do the following SELECT with SQL Server:

SELECT strName + ' has ' + intAge + ' age' FROM EmployeesTable

We find this error message:

 Mess. 245, Level 16, Status 1, Line 1 Conversion error converting the value nvarchar 'age' to data type int.

 

The solution is quite simple, if what you want is to concatenate, you must convert into string any numeric field included in the concatenation: 

SELECT strName + ' has ' + cast(intAge as varchar) + ' age' FROM EmployeesTablle

 

Also comment that this problem is specific of SQL Server, because SQL standard operator to concatenate is '||', which is used by other databases such as Oracle. MySQL, even when does not use the standard, concatenate with CONCAT() function

 

Thus, in both DB (Oracle and MySQL) the operator '+' is used just to add, so concatenating the parser will never understand you want to perform an arithmetic operation.