Conversion failed when converting the varchar value to int inside CONCAT

Question

Running this query:

SELECT CAST(CONCAT('BL.', IdBL,' - lgn.', LgnBL, ' \ BE.'+BE_Numero_carnet) AS varchar(1000)) AS chemin 
FROM ...

Results in an error:

Msg 245, Level 16, State 1
Conversion failed when converting the varchar value ' \ BE.' to data type int.

Answer 1

Dont use the +

SELECT CAST(CONCAT('BL.', IdBL,' - lgn.', LgnBL, ' \ BE.', BE_Numero_carnet) AS     varchar(1000)) AS chemin 
FROM ...

Answer 2

codeulike's answer is correct.
This answer is here to provide an explanation on why it is correct.

The + operator have a double meaning in SQL Server.
In one meaning, it's a string concatenation operator.
In the other meaning, it's an arithmetic addition operator.

Consider the following code:

DECLARE @A int = 1, @B int = 2,
        @X char(1) = '1', @Y char(1) = '2';

SELECT  @A + @B As Addition,
        @X + @Y As Concatenation

The result, as can be expected, is this:

Addition    Concatenation
3           12

So far, no surprises.
Now here is where it gets a little tricky: SQL Server makes extensive use of implicit conversion.

Basically, every time that two different data types are compared, combined, or assigned from one to the other, SQL Server will attempt to implicitly convert one data type to another.
This is not done arbitrarily - SQL Server has a well documented data type precedence table. SQL Server will attempt implicit conversion from the lower data type to the higher, and since int is located higher in that table than char - it will attempt to convert the char to int, and will fail if that's impossible.

Using the same variables from the script before:

SELECT  @A + @B As Addition,
        @X + @B As ImplicitConversionAndAdition;

will result with this:

Addition    ImplicitConversionAndAdition
3           3

Setting a char value to @x that can't be converted to int:

SET @X = 'a';

-- Remember: @Y is a char(1)
SELECT @X + @Y As Concatenation;

Will result with this:

Concatenation
a2

However, attempting @X + @B will result with a conversion error:

SELECT @X + @B As ImplicitConversionFailure

Will result with this:

Conversion failed when converting the varchar value 'a' to data type int.