Arithmetic Overflow in SQL Server

Question

I have a stored procedure which calculates the distance between two coordinate pairs as a float. I'm trying to use this to filter a list of values but getting an arithmetic overflow error. The query is:

SELECT * FROM Housing h  WHERE convert(float, dbo.CalculateDistance(35, -94, h.Latitude, h.Longitude)) <= 30.0

Which errors with:

Msg 8115, Level 16, State 6, Line 1 Arithmetic overflow error converting float to data type numeric.

The stored procedure for reference:

CREATE FUNCTION [dbo].[CalculateDistance]
    (@Longitude1 DECIMAL(8,5),
    @Latitude1   DECIMAL(8,5),
    @Longitude2  DECIMAL(8,5),
    @Latitude2   DECIMAL(8,5))
RETURNS FLOAT
AS
BEGIN
DECLARE @Temp FLOAT

SET @Temp = SIN(@Latitude1/57.2957795130823) * SIN(@Latitude2/57.2957795130823) + COS(@Latitude1/57.2957795130823) * COS(@Latitude2/57.2957795130823) * COS(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823)

IF @Temp > 1
    SET @Temp = 1
ELSE IF @Temp < -1
    SET @Temp = -1

RETURN (3958.75586574 * ACOS(@Temp) )

END

've also tried converting the result to decimal with no effect.

Answer 1

Your inputs are DECIMAL(8,5). This means that the equations consist of, for example, SIN(DECIMAL(8,5) / 57.2957795130823). Where 57.2957795130823 can not be represented as a DECIMAL(8,5).

This means that you have an implicat CAST operation due to the different data type. In this case, it would seem that the 57.2957795130823 is being cast to DECIMAL(8,5) [a numeric], and causing the overflow.

I would recommend any of these:
- Altering your function to take the inputs as FLOATS. Even if the function is called with numerics
- Changing 57.2957795130823 to 57.29577
- Explicitly casting the DECIMALs to FLOATs