Haversine formula in SQL This is an implementation of the Haversine formula in Microsoft Transact SQL.

How can I simplify the function?

CREATE FUNCTION dbo.Haversine(@point_a geography, @point_b geography)
RETURNS FLOAT
AS BEGIN
    DECLARE @result FLOAT
    DECLARE @lat1 FLOAT = @point_a.Lat
    DECLARE @lon1 FLOAT = @point_a.Long
    DECLARE @lat2 FLOAT = @point_b.Lat
    DECLARE @lon2 FLOAT = @POINT_b.Long

    DECLARE @earth_radius FLOAT =  6371
    DECLARE @dLat FLOAT = RADIANS(@lat2 - @lat1)
    DECLARE @dLon FLOAT = RADIANS(@lon2 - @lon1) 
    SET @lat1 = RADIANS(@lat1)
    SET @lat2 = RADIANS(@lat2)

    DECLARE @a FLOAT
    SET @a = POWER(SIN(@dLat/2),2) + COS(@lat1)*COS(@lat2)*POWER(SIN(@dLon/2),2)
    DECLARE @c FLOAT = 2*ASIN(SQRT(@a))
    SET @result = @earth_radius * @c;
    RETURN @result
END


Here is a test of the function:

DECLARE @target_point geography = (SELECT GeoLocation FROM WA_Features WHERE FEATURE_NAME = 'Seattle' AND FEATURE_CLASS='Populated Place');

SELECT FEATURE_NAME, dbo.Haversine(@target_point, GeoLocation)*0.62137 FROM WA_Features WHERE FEATURE_CLASS='Lake' ORDER BY dbo.Haversine(@target_point, GeoLocation);


Am I calling the function properly? And is there a way to call it only once per row?
Why do you need the function? To measure the distance (in miles) between two points? Then you want to select all points of a certain type and order them by distance from a given origin.

With a CTE (common table expression)  you can simplify things a lot too.

But, the real issue, is that you should use the functions already available in the geography datatype: STDistance. This STDistance function returns the 'LineString' distance, which is 0.25% accurate to the geodesic distance. Additionally, this is better than the 0.5% accuracy of the Haversine distance.

So, by the sounds of it, you have horribly overcomplicated this process. Consider this (SQLFiddle example here too):

Select s.FEATURE_NAME as SName,
       s.GeoLocation.Lat as SLat,
       s.GeoLocation.Long as SLong,
       t.FEATURE_NAME as TName,
       t.GeoLocation.Lat as TLat,
       t.GeoLocation.Long as TLong,
       s.GeoLocation.STDistance(t.GeoLocation) * 0.00062137 as Distance
from Features s,
     Features t
where s.FEATURE_NAME = 'Seattle'
  and s.FEATURE_CLASS = 'Populated Place'
  and t.FEATURE_CLASS = 'Lake'
order by Distance desc