CONCAT in SQL Server 2014

Question

I have a problem when I want to use CONCAT inside my stored procedure. I press execute and get this error message:

Mens 195, Nivel 15, Estado 10, Procedimiento upd_agregar, Línea 96'
CONCAT' is not a recognized built-in function name.

This is my table:

create table sucursales
(
    idSucursal  nvarchar(5) primary key,
    idEmpresa   int not null,
    sucursal    nvarchar(25) not null,
    direccion   nvarchar(100) not null,
    telefono    nvarchar(25),
    email       nvarchar(25) not null,
    constraint fk_suc_emp foreign key(idEmpresa) 
                references empresas(idEmpresa)
)

This is my query:

create procedure upd_agregar
    (@idEmpresa int, 
     @sucursal nvarchar(25),
     @direccion nvarchar(100),
     @telefono nvarchar (25),
     @email nvarchar(25))
as
    declare @longitud int, @codEmpresa nvarchar(2),@codSucursal nvarchar (2)

    --Generar codigo de empresa
    IF len(@idEmpresa) < 2
        SET @codEmpresa = CONCAT ('0',@idEmpresa)
    ELSE
        SET @codEmpresa = @idEmpresa
GO

and then when I want to make a select like this, I receive an error that I must declare a scalar variable @codSucursal:

SELECT @codSucursal = isnull(max(cast(substring (idSucursal,4,2)AS int)),0) + 1 FROM sucursales 
WHERE idEmpresa = @idEmpresa

score 2 · Answer 1 · answered May 03 '17 at 03:57

2

CONCAT was added in SQL Server 2012. Check the version of your server, maybe you are not using 2014 as you think:

SELECT @@VERSION

Besides, you don't really need CONCAT here and you don't need to check the LEN to convert an integer into a string with leading zeroes:

SET @codEmpresa = RIGHT('00' + CAST(@idEmpresa as nvarchar(2)), 2)

answered May 03 '17 at 03:57

Vladimir Baranov

31,799
5
53
90

I'm using the 2014 version – Gabriela Zeas Nuñez May 05 '17 at 23:24

score 0 · Answer 2 · answered May 03 '17 at 05:12

Try this:

create procedure upd_agregar
    (@idEmpresa int, @sucursal nvarchar(25),@direccion nvarchar(100),@telefono nvarchar (25),@email nvarchar(25))
    as
        declare @longitud int, @codEmpresa nvarchar(2),@codSucursal nvarchar (2)
        --Generar codigo de empresa
        IF len(CAST(@idEmpresa AS NVARCHAR(2))) < 2
        SET @codEmpresa = CONCAT ('0', CAST(@idEmpresa AS NVARCHAR(2)))
        ELSE
        SET @codEmpresa = @idEmpresa

        PRINT @codEmpresa
    GO

-- EXEC upd_agregar 1, 'B', 'C', 'D', 'E'
-- Result: 01

CONCAT in SQL Server 2014

2 Answers2