Home / Questions / 5228
Accepted
Juan Manuel Palacios
Asked: 2020-03-05 13:33:16 +0800 CST 2020-03-05 13:33:16 +0800 CST

Why do I get that my SQL statement is not a valid identifier?

  • 772

I'm putting together a statement INSERTin SQL with values ​​stored in variables that come from a CURSORthat I'm looping through. At the time of executing the instruction INSERTI am getting an invalid identifier error.

The code I'm working on is the following and it's inside a repeating loop that gets data from a cursor:

IF (@Estado = 'V')
    SET @NuevoEstado = 'ACT'
ELSE
    SET @NuevoEstado = 'ANU'

SET @ClienteId = (
        SELECT ClienteId
        FROM QueryClientesDescriptivo
        WHERE CodigoContribuyente = @RUC
            AND Estado = 'ACT'
        )
SET @MovimientoInventarioId = @MovimientoInventarioId + 1
SET @Query = 'INSERT INTO [COMPU90].[SIV3].[dbo].MovimientosInventario (CompaniaId, MovimientoInventarioId, TipoMovimientoInventarioId, Fecha, Glosa, AlmacenId, PersonaId, Estado, OrigenId, Migracion) ' + 'VALUES (' + CHAR(39) + '10043' + CHAR(39) + ',' + CHAR(39) + CONVERT(VARCHAR, @MovimientoInventarioId) + CHAR(39) + ',' + CHAR(39) + 'SVEN' + CHAR(39) + ',' + CHAR(39) + CONVERT(VARCHAR, @Fecha, 101) + CHAR(39) + ',' + CHAR(39) + '' + CHAR(39) + ',' + CHAR(39) + '2' + CHAR(39) + ',' + CHAR(39) + CONVERT(VARCHAR, @ClienteId) + CHAR(39) + ',' + CHAR(39) + @NuevoEstado + CHAR(39) + ',' + CHAR(39) + '0' + CHAR(39) + ',' + CHAR(39) + '0' + CHAR(39) + ')'

EXEC @Query

The error message I get is the following:

The name 'INSERT INTO [COMPU90].[SIV3].[dbo].MovementsInventory (CompanyId, MovementInventoryId, TypeMovementInventoryId, Date, Gloss, WarehouseId, PersonId, Status, OriginId, Migration) VALUES ('10043','141907',' SVEN','01/04/2016','','2','253624','ANU','0','0')' is not a valid identifier.

I have tried changing the format of the date, the way in which to pass the parameters but no way frees me from that error, however, when executing it directly it works without giving me errors.

sql

2 Answers

  1. Best Answer

    Add parentheses, on the line that executes the query:

    EXEC (@query)
    • 10

  2. You could also launch the query using the stored procedure sp_executesqlthat I mentioned in your other question

    The code you would use would be the following (in my example I make a loop to insert 10 records):

    /*CREATE TABLE MovimientosInventario (
    CompaniaId int, MovimientoInventarioId int, TipoMovimientoInventarioId varchar(64), Fecha datetime, 
    Glosa varchar(64), AlmacenId int, PersonaId int, Estado varchar(64), OrigenId int, Migracion bit)*/

DECLARE @Estado varchar(64), @NuevoEstado varchar(64), @Fecha datetime,
    @ClienteId int, @MovimientoInventarioId int

IF (@Estado = 'V') 
    SET @NuevoEstado = 'ACT' 
ELSE
    SET @NuevoEstado = 'ANU'

SET @ClienteId = 3
SET @MovimientoInventarioId = 0

while (@MovimientoInventarioId < 10) 
begin
    SET @MovimientoInventarioId = @MovimientoInventarioId + 1
    SET @Fecha = GETDATE()

    exec sp_executesql N'INSERT INTO MovimientosInventario (
            CompaniaId, MovimientoInventarioId, TipoMovimientoInventarioId, Fecha, 
            Glosa, AlmacenId, PersonaId, Estado, OrigenId, Migracion) 

        VALUES (
            @CompaniaId, @MovimientoInventarioId, @TipoMovimientoInventarioId, @Fecha, 
            @Glosa, @AlmacenId, @PersonaId, @Estado, @OrigenId, @Migracion)', 

        N'@CompaniaId int, @MovimientoInventarioId int, @TipoMovimientoInventarioId varchar(64), @Fecha datetime, 
        @Glosa varchar(64), @AlmacenId int, @PersonaId int, @Estado varchar(64), @OrigenId int, @Migracion bit', 

        @CompaniaId=10043, @MovimientoInventarioId=@MovimientoInventarioId, @TipoMovimientoInventarioId='SVEN', @Fecha=@Fecha, 
        @Glosa='', @AlmacenId=2, @PersonaId=@ClienteId, @Estado=@NuevoEstado, @OrigenId=0, @Migracion=0

end

    The advantage of doing it this way is that the code is much cleaner and more maintainable and that the parameters are typed.

    And probably more optimal because, being a parameterized query, there will be a single execution plan

    • 1