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
– andrewdski, Commented Jun 11, 2011 at 14:53

MatBailie · Accepted Answer · 2011-06-11 15:44:20Z

4

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

answered Jun 11, 2011 at 15:44

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

gbn Over a year ago

I thought about this: 57.2957795130823 is decimal(15,13). Dividing decimals follows some arcane rules: stackoverflow.com/questions/423925/…. Try SELECT SIN(CAST(-94 As decimal(8,5)) / 57.2957795130823 ) too

MyHeadHurts · Accepted Answer · 2011-06-11 14:54:25Z

1

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

answered Jun 11, 2011 at 14:54

MyHeadHurts

1,5725 gold badges37 silver badges89 bronze badges

1 Comment

gbn Over a year ago

SIN output is already float: and input are converted to float too

gbn · Accepted Answer · 2011-06-11 15:56:17Z

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, 2011 at 15:56

answered Jun 11, 2011 at 15:48

gbn

434k84 gold badges602 silver badges690 bronze badges

Comments

Denis de Bernardy · Accepted Answer · 2011-06-11 14:46:11Z

0

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

answered Jun 11, 2011 at 14:46

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Collectives™ on Stack Overflow

Arithmetic Overflow in SQL Server

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related