mercredi 27 décembre 2017

Column names don't match table using SELECT variables as INSERT values

I created a stored procedure that would INSERT/UPDATE when necessary. I am using this method to evaluate if i have to insert or not:

INSERT INTO X
SELECT @A, @B, @C -- Exactly as the table, all nullables
WHERE NOT EXISTS (SELECT * from ... WHERE condition)

But i am receiving the

Msg 213, Level 16, State 1, Procedure ArtigoEAN_Remover, Line 48 [Batch Start Line 7] Column name or number of supplied values does not match table definition.

This is my complete code (i signaled line 48):

@ArtigoID AS varchar(14),
@TipoArtigo AS varchar(3),
@CodBarras AS varchar(24)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
SET NOCOUNT ON;

    IF (@ArtigoID IS NOT NULL AND @TipoArtigo IS NULL) OR (@ArtigoID IS NULL AND @TipoArtigo IS NOT NULL)
    BEGIN
        PRINT 'Tem que escolher ArtigoID & TipoArtigo para remover código EAN da referência.'
        RETURN
    END
    --
    IF (@CodBarras IS NOT NULL) AND (@ArtigoID IS NOT NULL OR @TipoArtigo IS NOT NULL)
    BEGIN
        PRINT 'Escolher Código EAN apenas (Para remover esse código de barras de todas as referências) OU ArtigoID + TipoArtigo (remover todos os códigos EAN associados a essa referência)'
        RETURN
    END
    --
    IF @ArtigoID IS NOT NULL AND @TipoArtigo IS NOT NULL 
    BEGIN 

    UPDATE [s].[dbo].[FArtigo]
    SET CodBarras = ''
    WHERE ArtigoID = @ArtigoID AND TipoArtigo = @TipoArtigo AND CodBarras = @CodBarras
    -- LINE 48
    INSERT INTO [s].[dbo].[Infolog_ArtigoEAN] 
    SELECT @ArtigoID,@TipoArtigo,@CodBarras
    WHERE NOT EXISTS (SELECT * FROM [s].[dbo].[Infolog_ArtigoEAN] WHERE ArtigoID = @ArtigoID AND TipoArtigo = @TipoArtigo AND CodBarras = @CodBarras)
    -- Inserts above if nox exist, update below in any case.
    UPDATE [s].[dbo].[Infolog_ArtigoEAN] SET CodBarras = '' WHERE ArtigoID = @ArtigoID AND TipoArtigo = @TipoArtigo AND CodBarras = @CodBarras

    INSERT INTO [s].[dbo].[ArtigoEAN]
    SELECT @ArtigoID,@TipoArtigo,@CodBarras
    WHERE NOT EXISTS (SELECT * FROM [s].[dbo].[Infolog_ArtigoEAN] WHERE ArtigoID = @ArtigoID AND TipoArtigo = @TipoArtigo AND CodBarras = @CodBarras)
    UPDATE [s].[dbo].[ArtigoEAN] SET CodBarras = '' WHERE ArtigoID = @ArtigoID AND TipoArtigo = @TipoArtigo AND CodBarras = @CodBarras

    END
    --
    IF @CodBarras IS NOT NULL AND (@ArtigoID IS NULL AND @TipoArtigo IS NULL) 
    BEGIN

    UPDATE [s].[dbo].[FArtigo] SET CodBarras = '' WHERE CodBarras = @CodBarras
    UPDATE [s].[dbo].[Infolog_ArtigoEAN] SET CodBarras = '' WHERE CodBarras = @CodBarras
    UPDATE [s].[dbo].[ArtigoEAN] SET CodBarras = '' WHERE CodBarras = @CodBarras

    END
END

So my question is, why is it trying to do the INSERT after line 48 when i am just ALTERING (saving) the procedure, i'm not even running it with parameters.

Aucun commentaire:

Enregistrer un commentaire