CODEWITHSUNDEEP
sql-server
strange_098
strange_098

Reputation: 1391

Error converting data type varchar to numeric. SQL Server

I'm writing data on the Sql Server database. When data is written, a trigger runs.

TRIGGER

ALTER TRIGGER Alert ON records AFTER INSERT AS
BEGIN

DECLARE @tempmin decimal = 0
DECLARE @current_max_idAlarm int = (SELECT MAX(IdAlarm) FROM alarms)
DECLARE @maxidAlarm int
DECLARE @temp decimal = (SELECT s.lim_inf_temp  from sensores s JOIN inserted i ON s.idSensor=i.idSensor )

-- Insert into alares from the inserted rows if temperature less than tempmin
SET IDENTITY_INSERT alarmes On
INSERT alarmes (IdAlarm, desc_alarm,date, idRecord)
    SELECT
    ROW_NUMBER() OVER (ORDER BY i.idRecord) + @current_max_idAlarm, 'Temp Error', GETDATE(), i.idRecord
    FROM
inserted AS i
    WHERE
i.Temperature < @temp

end

INSERT

insert into record values ('2014-05-26' ,'14:51:47','Sensor01','---','48.6','9.8');

Whenever I try to record this type of data: '---'

Gives the following error: 

Msg 8114, Level 16, State 5, Procedure Alert, Line 
Error converting data type varchar to numeric.

I know it is to be in decimal type (DECLARE @temp decimal - TRIGGER), but moving to the type varchar to trigger stops working correctly.

Does someone can help me resolve this error please?

Thank you all.

Upvotes: 1

Views: 29643

Answers (1)

mordack550
mordack550

Reputation: 500

You are trying to insert --- inside a numeric column, you simply can't do that.

You have mainly 2 options:

  • Change the data type of the destination column
  • Choose a different value to insert (like NULL)

Upvotes: 2

Related Questions