current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a trigger which executes after a record is inserted into a table. The trigger basically, calls a function and passes values from the insert into it. However, while it works some times, I also keep getting the following error:

Msg 8115, Level 16, State 2, Procedure UpdateNabersRating, Line 17
Arithmetic overflow error converting expression to data type int.

The trigger is as follows:

UPDATE BMS_Snapshot SET
    NABERS = dbo.SetmetricsNABERSCalculation(
        60,
        tbl.NetFloorArea,
        tbl.ElectricityCumulative,
        tbl.GasCumulative,
        tbl.ElectricityCumulativeMax,
        tbl.GasCumulativeMax,
        tbl.ElectricityMaxTotal,
        tbl.GasMaxTotal,
        tbl.NaturalGasConversionCubicMetersToMJ,
        tbl.SuburbId)
FROM
    (SELECT 
        Snap.SnapshotId AS SnapshotId,
        Snap.ElectricityCumulative AS ElectricityCumulative,
        Snap.GasCumulative AS GasCumulative,
        Building.NetFloorArea AS NetFloorArea, 
        Building.NABERSElecTotalMax AS ElectricityMaxTotal,
        Building.NABERSGasTotalMax AS GasMaxTotal,
        Building.NABERSExpiry As Expiry,
        NABERSTarget.ElecCumulativeMax AS ElectricityCumulativeMax,
        NABERSTarget.GasCumulativeMax AS GasCumulativeMax,
        [State].NaturalGasConversionCubicMetersToMJ AS NaturalGasConversionCubicMetersToMJ,
        Suburb.SuburbId AS SuburbId
     FROM inserted AS Snap 
        INNER JOIN AST_Building AS Building ON Snap.BuildingId = Building.BuildingId
        INNER JOIN ESD_Suburb AS Suburb ON Building.SuburbId = Suburb.SuburbId
        INNER JOIN ESD_State AS [State] ON Suburb.StateId = [State].StateId
        INNER JOIN AST_NABERSTarget NABERSTarget ON Snap.BuildingId = NABERSTarget.BuildingId AND
            Snap.TimeStamp = NABERSTarget.Timestamp
        /*Where Snap.SnapshotId IN (Select inserted.SnapshotId FROM inserted)*/) AS tbl
WHERE tbl.SnapshotId = BMS_Snapshot.SnapshotId AND BMS_Snapshot.Range = 1

I've been playing around with it for a while, but can't seem to put my finger on the problem, particularly given it works sometimes.

share|improve this question
    
It is impossible to say just by looking at the query. You probably have an implicit type conversion somewhere. It could be that a parameter to the function is declared as int and you pass a varchar field as an argument that most of the times has a number in it but sometimes it has text. So you just have to check your data types. –  Mikael Eriksson May 25 '12 at 6:16
    
Thanks Mikael, you actually helped me fix the problem. I've spent so much time looking at the query, I forgot about that. –  Dan May 25 '12 at 6:26

1 Answer 1

up vote 2 down vote accepted

Changed, arguments on function to FLOAT, instead of INT for two of the parameters.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.