Concatenate int and varchar is a usual problem that we face in SQL scripting and the solution is easily available over the web. But what encourages me to write this article is, we can resolve this issue using a built in function in SQL Server 2012.
Let me show you the issue that we generally face when we concatenate int and varchar. Given below is the script.
Use AdventureWorks2012 GO SELECT [DepartmentID] + ' ' + [Name] AS [Department ID & Name] FROM [HumanResources].[Department] GO --OUTPUT
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value ‘Document Control’ to data type smallint.
Solution 1:
In the earlier version of SQL Server, we usually use CONVERT function to convert int into varchar and then concatenate it.
Given below is the script.
Use AdventureWorks2012 GO SELECT CONVERT(VARCHAR(5),[DepartmentID]) + ' ' + [Name] AS [Department ID & Name] FROM [HumanResources].[Department] --OUTPUT
Solution 2:
In this solution, we will use CONCAT function (a newly shipped function in SQL Server 2012) to convert int into varchar then concatenate it.
Given below is the script.
Use AdventureWorks2012 GO SELECT CONCAT([DepartmentID] , ' ' , [Name] ) AS [Department ID & Name] FROM [HumanResources].[Department] --OUTPUT
Conclusion :
I generally recommend solution 2 because as you can see that there is no difference in output between solution 1 and solution 2 but there is a remarkable difference between both solutions in performance. You can get the performance details from here.
SQL Server Portal 