When combining Data Types it is important to note that there is a data type precedence used to determine how they are converted and combined. Data types with a lower precedence are implicitly converted to the data type with the higher precedence.
If a data type of a higher precedence is needed then an explicit conversion must be made using either the CAST or CONVERT functions. Here is an excellent conversion chart that I have posted previously that will help in determining which data type can or cannot be converted.
The main difference between the CAST and CONVERT functions is that CAST is an ISO standard where as CONVERT is specific to Transact-SQL. In addition the CONVERT function can only using styles for numbers and dates.
Now to explain the above code. The first two lines we are creating two variables. One as a tiny integer and the other as a character data type. On line 5 we use code to concatenate the two variables. Since the character variable can be converted to an integer SQL will perform an implicit conversion and will display the following result.
Next on line 8 is where we type out actual text that cannot be converted into an integer. Since a character data type has a lower precedence than an integer and SQL cannot perform the implicit conversion and will display the following result.
So to combine the text to the integer, the integer will have to be explicitly converted as shown on lines 11 and 14. In both cases the following result will be displayed.