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.

It says it is converting float to numeric. I don't see anywhere where that should be happening, except *maybe* the select itself when it is formatting the results? — andrewdski, Jun 11 '11 at 14:53

score 4 · Accepted Answer · answered Jun 11 '11 at 15:44

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

I thought about this: 57.2957795130823 is decimal(15,13). Dividing decimals follows some arcane rules: http://stackoverflow.com/questions/423925/t-sql-decimal-division-accuracy/424052#424052. Try SELECT SIN(CAST(-94 As decimal(8,5)) / 57.2957795130823 ) too — gbn, Jun 11 '11 at 15:47

score 1 · Answer 2 · answered Jun 11 '11 at 14:54

I would try converting some of my arithmetic just in case

convert(float,(SIN(@Latitude1/57.2957795130823)) * convert(float,(SIN(@Latitude2/57.2957795130823)) + convert(float,(COS(@Latitude1/57.2957795130823)) * convert(float,(COS(@Latitude2/57.2957795130823)) * convert(float,COS(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823))

another thing you could use is the

IFNULL(convert(float,(SIN(@Latitude1/57.2957795130823)),0.00)

your results may be returning nulls

SIN output is already float: and input are converted to float too — gbn, Jun 11 '11 at 15:49

gbn · Answer 3 · 2011-06-11T15:56:17.020

1

It's your comparison to <= 30.0

30.0 is decimal(3,2) (Constants with decimal points are decimal in SQL Server) and the float output won't cast. See:

SELECT 30.0 AS What INTO dbo.DataType
Go
SELECT t.name, c.*
FROM sys.columns c JOIN sys.types t ON c.system_type_id = t.system_type_id
WHERE object_id = OBJECT_ID('dbo.DataType')
GO
DROP TABLE dbo.DataType
GO

Try

... <= CAST(30.0 As float)

edited Jun 11 '11 at 15:56

answered Jun 11 '11 at 15:48

gbn

422,506
82
585
676

score 0 · Answer 4 · answered Jun 11 '11 at 14:46

0

You're returning a float. Shouldn't you be using floats for the latitude and longitude variables as well?

answered Jun 11 '11 at 14:46

Denis de Bernardy

75,850
13
131
154

Arithmetic Overflow in SQL Server

4 Answers4