Feeds:
Posts
Comments

Posts Tagged ‘SQL Server 2012 – CONCAT’

SQL SERVER 2012 – Simple way to add variables in the dynamic SQL

Posted in Solutions, SQL SERVER, tagged , , , , on September 7, 2013| Leave a Comment »

Sometimes, you need to construct (n) number of result set from (n) number of inputs using single query and here the dynamic SQL concept comes handy. In some solutions, I really feel that it is very hard to ignore dynamic SQL. (Never ever execute the user input directly using dynamic SQL. Always validate it before execution.) But it has its own rule to construct and you must follow it.

Given below is the list of actions that you must take care while adding variable in the dynamic SQL.

  • If you pass a NULL value of a variable in the dynamic SQL the whole string will be NULL, you must handle NULL variables in the dynamic SQL.
  • If you pass any data type variables other than varchar/ nvarchar, it will generate error. You must convert it into varchar/nvarchar before using it into dynamic SQL.
  • you must use concatenation sign ( + ) to concatenate the dynamic SQL with variables. You cannot place it directly inside the the dynamic SQL.
  • You must be very careful while opening and closing single quotes (‘) in the dynamic SQL. If you forget even a single place you cannot execute dynamic SQL.

Given below are the two approaches. 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. I came across this approach recently, built some dynamic SQL on it and found it very good.

Old Approach :
Given below is the script we usually use to construct dynamic SQL.

--This script is compatible with all versions of SQL Server
DECLARE @SQL as varchar(max)
DECLARE @Datetime as datetime

SET @Datetime='2005-07-01 00:00:00.000'

SET @SQL='SELECT * FROM Sales.SalesOrderHeader
WHERE [OrderDate]=''' + CONVERT(varchar(50),@Datetime) + ''''
PRINT @SQL
EXEC (@SQL)

New Approach :
In the new approach, we need to use the CONCAT FUNCTION (shipped in SQL Server 2012) and it will resolve the above described problems.
Given below is the script.

--This script is compatible with SQL Server 2012 and above.
DECLARE @SQL as varchar(max)
DECLARE @Datetime as datetime

SET @Datetime='2005-07-01 00:00:00.000'

SET @SQL=CONCAT('SELECT * FROM Sales.SalesOrderHeader
WHERE [OrderDate]=','''',@Datetime,'''')
PRINT @SQL
EXEC (@SQL)

Conclusion :
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. 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.

Let me know your opinion about new approach and its pros and cons.

Read Full Post »

SQL SERVER – How to concatenate int and varchar

Posted in Solutions, SQL SERVER, tagged , , , , on July 31, 2013| 2 Comments »

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

conactenate int to varchar1.1

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

conactenate int to varchar1.1

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.

Read Full Post »

SQL Server 2012 – String Function – CONCAT

Posted in Functions, SQL SERVER, tagged , , , on September 11, 2012| 6 Comments »

SQL Server 2012 has introduced a new string function namely “CONCAT”. It is a very handy & important function to deal with the string concatenation.

Let me explain its syntax, parameters, purpose and examples in detail.
Syntax :

CONCAT ( string_value1, string_value2 [, string_valueN ] )

Parameters :
@string_value1 : First string value to concatenate with other strings.
@string_value2 : Second string value to concatenate with other strings.
It will go up to (n) Numbers of strings.

Purpose :
It concatenates two or more than two strings and returns as a single string value. Minimum we need to pass two strings to concatenate otherwise it will generate an error. It implicitly converts parameters to string. If The parameters are NULL it treats it as empty string of varchar(1).

Let me explain this with simple examples.

Example-1 : CONCAT – String values

Select Concat('SQL Server ','2012') as [Result]

Result
---------------
SQL Server 2012

(1 row(s) affected)

Example-2 : CONCAT – Integer values
In this example, “CONCAT” function will implicitly convert integer data type to string data type and concatenate it.

Select CONCAT(1,2) as [Result]

Result
------------------------
12

(1 row(s) affected)

Example-3 : CONCAT – Numeric values
In this example, “CONCAT” function will implicitly convert numeric data type to string data type and concatenate it.

Select CONCAT(12.34,56.78) as [Result]

Result
----------------------------------------------------------------------------------
12.3456.78

(1 row(s) affected)

Example-4 : CONCAT – Less than 2 strings 

Select Concat('SQL Server ') as [Result]

Msg 189, Level 15, State 1, Line 1
The concat function requires 2 to 254 arguments.

Example-5 : CONCAT – Passed NULL as parameter
It converts NULL to empty string of varchar(1) and return it.

Select Concat('SQL Server ',NULL) as [Result]

Result
---------------
SQL Server

(1 row(s) affected)

Example-6 : CONCAT – Passed NULL Values from Database
Before in SQL server 2012, we had to use ISNULL to convert NULL to empty string & concatenate.
But this function resolves this issue. It automatically converts NULL to empty string. Lets use Adventure database to test it.

Select top 10 [FirstName] ,  [MiddleName] , [LastName]
,[FirstName] +  [MiddleName] + [LastName] as [Full Name]
from [Person].[Person]

In the above example, due to the NULL values in the middle name column SQL failed to concatenate it and return NULL as [Full Name]

Lets try to do the same with “CONCAT” function.

Select top 10 [FirstName] ,  [MiddleName] , [LastName]
,Concat([FirstName] , [MiddleName] , [LastName]) as [Full Name]
from [Person].[Person]

In the above example, CONCAT function replaced NULL with emprty string and concatenates the FULL Name successfully.

Reference : MSDN

Read Full Post »