More than 2 columns in a CONCAT function

Question

In SQL Server 2012 I want to concat 5 columns into 1 but in the query it works but when I put in in a view it gives me an error like

Msg 174, Level 15, State 1, Line 3
The CONCAT function requires 2 argument(s).

What's the problem so I can fix it because concat is a good function for concatenate more than 1 column because if its null they make it empty..

CODE:

SELECT        
   'Aan ' + A.Name AS 'Naam', 
   { fn CONCAT('T.a.v. ', C.Salutation + ' ', C.FirstName + ' ', C.MiddleName + ' ', C.LastName) } AS 'T.a.v.',   
   ISNULL(ISNULL(A.Address1_Line2, A.Address1_Line1), 
   C.Address1_Line2) AS 'Adres', 
   ISNULL(A.Address1_PostalCode + ' ' + A.Address1_City, A.Address2_PostalCode + ' ' + A.Address2_City) AS 'Woonplaats',
   'heer' + ' ' + ISNULL(C.MiddleName + ' ', N'') + ISNULL(C.LastName, N'') AS 'Aanhef'  
FROM            
    dbo.Account AS A 
FULL OUTER JOIN  
    dbo.Contact AS C ON A.Name = C.AccountIdName  
WHERE 
    (C.Salutation = 'Dhr.') AND (A.Name IS NOT NULL) AND (A.StatusCode = 1) 
    AND (ISNULL(C.StatusCode, 1) = 1) OR (C.Salutation = 'dhr.') AND (A.Name IS NOT NULL) AND (A.StatusCode = 1) AND (ISNULL(C.StatusCode, 1) = 1)     

Answer 1

Hy. If you want to use CONCAT as a canonical function {fn CONCAT(...)} into view designer, there is a work-around solution in order to concatenate more than two columns/chars.

You can use CONCAT inside CONCAT like this:

Let's suppose you want to concatenate two codes into a single one with a "-" between

column1 = 123456

column2 = 0001

{fn CONCAT({fn CONCAT(column1, '-')}, column2)}

As a result you will have: 123456-0001

Answer 2

There must be an error somewhere else in your view!!

Ok, then what I did with your code was to change this line

{ fn CONCAT('T.a.v. ', C.Salutation + ' ', C.FirstName + ' ', C.MiddleName + ' ', C.LastName) } AS 'T.a.v.'

to this

CONCAT('T.a.v. ', C.Salutation + ' ', C.FirstName + ' ', C.MiddleName + ' ', C.LastName) AS 'T.a.v.'

Edit:

Just to explain the difference in code, is that the one with { fn ....} is a Canonical function and microsoft promise that it will work on all ODBC connections.

From MSDN:

Canonical functions are functions that are supported by all data providers, and can be used by all querying technologies. Canonical functions cannot be extended by a provider.