Try this one below:

Might be some logical mistakes in your function

This function allows you to calculate the distance in miles or kilometers between any two points on the earth. I created to calculate the distance between two zip codes but is can be used for any two coordinates for which latitude and longitude is known.

NOTE: A database for the coordinates of any US postal code can be obtained for free from Source Forge in CSV format. The URL is

http://prdownloads.sourceforge.net/zips/zips.csv.zip?download

Referred from http://www.sqlservercentral.com/scripts/Miscellaneous/31673/

Posted the entire thing over here since you may not have a login id for that site.

Might be some logical mistakes in your function

This function allows you to calculate the distance in miles or kilometers between any two points on the earth. I created to calculate the distance between two zip codes but is can be used for any two coordinates for which latitude and longitude is known.

NOTE: A database for the coordinates of any US postal code can be obtained for free from Source Forge in CSV format. The URL is

http://prdownloads.sourcef

Referred from http://www.sqlservercentra

Posted the entire thing over here since you may not have a login id for that site.

```
IF EXISTS (SELECT *
FROM sysobjects
WHERE name = N'CalcDistanceBetweenGeoLocations')
DROP FUNCTION dbo.CalcDistanceBetweenGeoLocations
GO
/**************************************************************************
DESCRIPTION: Gets the distance in miles or kilometers between two points on the earth
PARAMETERS:
@LatitudeA - Latitude of first point
@LongitudeA - Longitude of first point
@LatitudeB - Latitude of second point
@LongitudeB - Longitude of second point
@InKilometers - 1 if return distance in kilometers
0 if return distance in miles
RETURNS:
Float value of distance between two points in miles or kilometers
USAGE: DECLARE @LatitudeA FLOAT,
@LongitudeA FLOAT,
@LatitudeB FLOAT,
@LongitudeB FLOAT
SELECT @LatitudeA = latitude, @LongitudeA = longitude FROM zip_code WHERE zip_code = '92121'
SELECT @LatitudeB= latitude, @LongitudeB = longitude FROM zip_code WHERE zip_code = '92008'
SELECT dbo.CalcDistanceBetweenGeoLocations(@LatitudeA, @LongitudeA,
@LatitudeB, @LongitudeB,
0) as [miles]
SELECT dbo.CalcDistanceBetweenGeoLocations(@LatitudeA, @LongitudeA,
@LatitudeB, @LongitudeB,
1) as [kilometers]
AUTHOR: Karen Gayda
DATE: 04/12/2006
MODIFICATION HISTORY:
WHO DATE DESCRIPTION
--- ---------- ---------------------------------------------------
***************************************************************************/
CREATE FUNCTION dbo.CalcDistanceBetweenGeoLocations
(@LatitudeA FLOAT = NULL,
@LongitudeA FLOAT = NULL,
@LatitudeB FLOAT = NULL,
@LongitudeB FLOAT = NULL,
@InKilometers BIT = 0
)
RETURNS FLOAT
AS
BEGIN
DECLARE @Distance FLOAT
SET @Distance = (SIN(RADIANS(@LatitudeA)) *
SIN(RADIANS(@LatitudeB)) +
COS(RADIANS(@LatitudeA)) *
COS(RADIANS(@LatitudeB)) *
COS(RADIANS(@LongitudeA - @LongitudeB)))
--Get distance in miles
SET @Distance = (DEGREES(ACOS(@Distance))) * 69.09
--If specified, convert to kilometers
IF @InKilometers = 1
SET @Distance = @Distance * 1.609344
RETURN @Distance
END
GO
```