September 24, 2004 at 10:08 am
The error I get is: A Domain Error Occurred for the below stored procedure.
When I researched it on the Internet, it said this is because the "ACOS" value is not in the -1 and 1 range. So, my questions are:
How can I return the "Acos" value that you see in Red?
Since the value is obviously not in the -1 to 1 range, what can I do to fix this scenario?
CREATE PROCEDURE dbo.sp_searchLocations
@ACX numeric(9),
@Radius numeric(9)
As
Declare @SinLat float(8)
Declare @CosLat float(8)
Declare @PhoneLong float(8)
select @SinLat = SinLat, @CosLat = CosLat, @PhoneLong = LonDeg from Phone where ACX = @ACX
select l.* from Dealer l, Phone z where z.ACX = left(l.Phone,6) And
12426.2 * Acos(@SinLat * SinLat + @CosLat * CosLat * cos(dbo.deg2rad(@PhoneLong - londeg))) / pi() <= @Radius
order by
type,
Acos(@SinLat * SinLat + @CosLat * CosLat * cos(dbo.deg2rad(@PhoneLong - londeg)))
GO
September 24, 2004 at 11:54 am
Discounting the fact that nothing was in Red so I may be missing something important, you ultimately are summing values that apparently aren't bounded within -1 to 1 (OK, that was obvious). Assuming that anything less that -1 should be treated as -1 and anything greater that 1 should be considered a 1 you can create a function that does that conversion.
Pseudo code:
CREATE FUNCTION dbo.MinusOneToOne(@TheFloat float)
RETURNS float
AS
BEGIN
declare @myReturn float
if @TheFloat < -1 set @myReturn = -1
else if @TheFloat > 1 set @myReturn = 1
else set @myReturn = @TheFloat
return @myReturn
END
And then call it like:
Acos(dbo.MinusOneToOne(@SinLat * SinLat + @CosLat * CosLat * cos(dbo.deg2rad(@PhoneLong - londeg))))
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply