Let me explain its syntax, parameters, purpose and examples in detail.ĬONCAT ( string_value1, string_value2 ) It is a very handy & important function to deal with the string concatenation. SQL Server 2012 has introduced a new string function namely “CONCAT”. You can get the performance details from here. 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. In this solution, we will use CONCAT function (a newly shipped function in SQL Server 2012) to convert int into varchar then concatenate it. In the earlier version of SQL Server, we usually use CONVERT function to convert int into varchar and then concatenate it. SELECT + ' ' + AS Ĭonversion failed when converting the nvarchar value ‘Document Control’ to data type smallint. Let me show you the issue that we generally face when we concatenate int and varchar. 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 know your opinion about new approach and its pros and cons.Ĭoncatenate int and varchar is a usual problem that we face in SQL scripting and the solution is easily available over the web. Also, it will be easier to debug as well because it will be in parts so you can easily split at the time of debugging. The performance will be same for both approaches but the new approach will be the shortest way to write dynamic SQL because it handles the above dynamic SQL issues internally. This script is compatible with SQL Server 2012 and above. In the new approach, we need to use the CONCAT FUNCTION (shipped in SQL Server 2012) and it will resolve the above described problems. This script is compatible with all versions of SQL Server Given below is the script we usually use to construct dynamic SQL.
I came across this approach recently, built some dynamic SQL on it and found it very good.
The old approach we usually follow in all versions of SQL Server and the new approach, we can follow in SQL Server 2012 and above. If you forget even a single place you cannot execute dynamic SQL.
For example, the following code will cause an error: SELECT 'Adventure' += 'Works'Įxamples A. Also, SET = + + is equivalent to SET = + + += operator cannot be used without a variable.
SET += 'expression' is equivalent to SET = + ('expression'). Returns the data type that is defined for the variable. Is any valid expression of any of the character data types. To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.