Numeric to Numeric Conversion Error

Asked

Viewed 33 times

1

I have a stored file in the database that changes a record of a customer, entering the date on which it will be visited and the interviewer who will make the visit.

CREATE PROCEDURE dbo.Captacao_InsereDadosAgendamento
@DATA_ENTREVISTA DATETIME,
@ENTREVISTADOR NVARCHAR(30),
@CGC NVARCHAR(14)
AS
BEGIN
    UPDATE
        CLIENTES
    SET
        DATA_AGENDAMENTO = @DATA_ENTREVISTA,
        ENTREVISTADOR = UPPER(@ENTREVISTADOR)
    WHERE
        CGC = @CGC
END

But when I run the trial, even if I pass the three parameters correctly, I get the following error:

Arithmetic overflow error Converting Numeric to data type Numeric. The statement has been terminated.

None of the parameters of the Procedure or table are of the Numeric type. What do I do to correct that mistake?

sql-server stored-procedures

  • What is the type of the date scheduling columns, interviewer and cgc?

  • date scheduling is datetime, interviewer is nvarchar(30) and cgc is nvarchar(14)

  • What are the data being passed on the variables? I believe that the problem may be in datetime. Try to delete it from your trial to take a test

  • I tried to pass just the interviewer’s name and it makes the same mistake

  • Can show how you are calling proc and what values?

  • I run the Procedure in SQL Manager Lite and pass the parameters directly in the pop-up it opens: https://drive.google.com/file/d/1M5foCBETcb2vxzy5EmbaFybsfdJx4AwO/view?usp=sharing

Show 1 more comment

1 answer

0


I’m putting the answer so I can close the question.

Talking to one of the analysts, we found a Rigger that was triggered in the Insert or update in this client table, to perform the processing of the data entered in the table. As none of this data is used in this database, we decided to use the DISABLE TRIGGER and ENABLE TRIGGER commands. And it worked. The new code looked like this:

CREATE PROCEDURE [dbo].[Captacao_InsereDadosAgendamento]
@DATA_ENTREVISTA DATETIME,
@ENTREVISTADOR NVARCHAR(30),
@CGC NVARCHAR(14)
AS
BEGIN
    DISABLE TRIGGER CLIENTES.CLIENTES_triu ON CLIENTES;  
    UPDATE
        CLIENTES
    SET
        DATA_AGENDAMENTO = @DATA_ENTREVISTA,
        ENTREVISTADOR = UPPER(@ENTREVISTADOR)
    WHERE
        CGC = @CGC;
    ENABLE TRIGGER CLIENTES.CLIENTES_triu ON CLIENTES;          
END

Thank you to those who tried to help!

Browser other questions tagged sql-server stored-procedures

You are not signed in. Login or sign up in order to post.