An invalid floating point operation occurred. SQL Server 2008

Question

I have weird problem with this code: if I run it like shown below I get error:

An invalid floating point operation occurred.

But if I change parameter @Longitude to -98.508730 (notice only last digit changed) code works just fine.

The code is supposed to lists properties in @MilesRadius around some LatLng point.
@Latitude and @Longitude parameters are of the same type as longitude and latitude fields in table Address.

What can I do here? Thanks.

DECLARE @Latitude decimal (10,6);
DECLARE @Longitude decimal (10,6);
DECLARE @MilesRadius int;
SET @Latitude = 29.607654
SET @Longitude = -98.508731
SET @MilesRadius  = 5

SELECT     ADR.LineOne as address, 
           ADR.City as city,           
           ADR.Latitude as latitude,
           ADR.Longitude as longitude, 
           ((3959 * acos(cos(radians(@Latitude)) * cos(radians(ADR.Latitude)) * cos(radians(ADR.Longitude) - radians(@Longitude)) + sin(radians(@Latitude)) * sin(radians(ADR.Latitude))))) as distance
FROM       Shared.Address ADR
WHERE      ADR.Latitude IS NOT NULL AND 
           ADR.Longitude IS NOT NULL AND 
           (3959 * acos(cos(radians(@Latitude)) * cos(radians(ADR.Latitude)) * cos(radians(ADR.Longitude) - radians(@Longitude)) + sin(radians(@Latitude)) * sin(radians(ADR.Latitude)))) < @MilesRadius                       
ORDER BY distance

Kilan

You are missing the part of your query where you declare the variables, please add that to your question – Sheilasheilah

I did. Sorry forgot that. Parameters Latitude and Longitude are of the same type as latitude and longitude fields in Address table. Decimal(10,6) – Kilan

Answer 1

The only function you are using that returns a domain error is ACOS and that occurs when the input is not in the range -1 to +1 so you can just fiddle this case (I'm assuming the intermediate expression is something like 1.000000000001 due to rounding errors)

SELECT     ADR.LineOne as address, 
           ADR.City as city,           
           ADR.Latitude as latitude,
           ADR.Longitude as longitude, 
           distance
FROM       Shared.Address ADR
CROSS APPLY (SELECT cos(radians(@Latitude)) * cos(radians(ADR.Latitude)) * cos(radians(ADR.Longitude) - radians(@Longitude)) + sin(radians(@Latitude)) * sin(radians(ADR.Latitude))) T(ACosInput)
CROSS APPLY (SELECT ((3959 * acos(CASE WHEN ABS(ACosInput) > 1 THEN SIGN(ACosInput)*1 ELSE ACosInput END)))) T2(distance)
WHERE      ADR.Latitude IS NOT NULL AND 
           ADR.Longitude IS NOT NULL AND 
           distance < @MilesRadius                       
ORDER BY distance

Aglow