Solved

# Help in writing user defined Function

Posted on 2004-08-20

I am trying to convert an ASP function into a user defined function in SQL Server 2000. This is the ASP code:

Function DistCalc2(lat1,lon1,lat2,lon2)

A = lat1/57.29577951

B = lon1/57.29577951

C = lat2/57.29577951

D = lon2/57.29577951

IF A = C AND B = D THEN

DistCalc2 = 0

ELSE

IF (SIN(A)*SIN(C) + COS(A)*COS(C)*COS(B-D)) > 1 THEN

DistCalc2 = 3963.1*Arccos(1)

ELSE

DistCalc2 = 3963.1*Arccos(SIN(A)*SIN(C) + COS(A)*COS(C)*COS(B-D))

End IF

End If

DistCalc2 = 1.6094 * DistCalc2

End Function

Using some transact sql resources I created the following function which keeps on giving syntax errors:

CREATE FUNCTION dbo.geo_distance (@lon1 decimal, @lat1 decimal, @lon2 decimal, @lat2 decimal)

RETURNS decimal

AS

IF @lat1 = @lat2 AND @lon1 = @lon2

BEGIN

DECLARE @dist decimal

SET @dist = 0

RETURN @dist

END

ELSE

IF (SIN(@lat1/57.29577951)*SIN(@lat2/57.29577951) + COS(@lat1/57.29577951)*COS(@lat2/57.29577951)*COS((@lon1/57.29577951)-(@lon2/57.29577951))) > 1

BEGIN

SET @dist = (3963.1*ACOS(1)) * 1.6094

RETURN @dist

END

ELSE

BEGIN

SET @dist = (3963.1 * ACOS(SIN(@lat1/57.29577951)*SIN(@lat2/57.29577951) + COS(@lat1/57.29577951)*COS(@lat2/57.29577951)*COS((@lon1/57.29577951)-(@lon2/57.29577951)))) * 1.6094

RETURN @dist

END