Here is a user-defined T-SQL function for the distance between two geographical locations. The math comes from http://mathforum.org/library/drmath/view/51879.html. I’m using type real for my parameters; all the calculations are done as float with implicit type conversion.

— ======================
— great circle distance in km between two geo points
— where lat and lon are like (47.65, -122.17)
— uses the haversine (half-versed sine) formula

create function DistanceBetween(@lat1 real, @lon1 real,
  @lat2 real, @lon2 real)
returns real
as
begin
  declare @result real
  declare @lat1InRadians real
  declare @lon1InRadians real
  declare @lat2InRadians real
  declare @lon2InRadians real
  declare @deltaLatitudes real
  declare @deltaLongitudes real
  declare @a real
  declare @c real
 
  set @lat1InRadians = @lat1 * ( PI() / 180.0)
  set @lon1InRadians = @lon1 * ( PI() / 180.0)
  set @lat2InRadians = @lat2 * ( PI() / 180.0)
  set @lon2InRadians = @lon2 * ( PI() / 180.0)
 
  set @deltaLatitudes = @lat2InRadians – @lat1InRadians
  set @deltaLongitudes = @lon2InRadians – @lon1InRadians
 
  set @a = POWER(SIN(@deltaLatitudes / 2.0), 2.0) + COS(@lat1InRadians) *
    COS(@lat2InRadians) * POWER(SIN(@deltaLongitudes / 2.0), 2.0)
  
  set @c = 2.0 * ATN2( SQRT(@a), SQRT(1.0 – @a) )
 
  set @result = 6370.0 * @c — radius of earth approx. 6370.0 km
 
  return @result
end

In SQL Server Management Studio, I right-clicked on the Programmability | Functions | Scalar-valued Functions folder icon in the database I was working with, and selected New Scalar-valued Function from the context menu. I deleted the template code and typed the code above. I hit F5 to create the code and then did a View | Refresh to verify that the new function had been created. (I had to delete several times with the command ‘drop function dbo.DistanceBetween’ during development). To test the DistanceBetween function I went to a new query window and typed:

use myWorkingDatabase
go

declare @lat1 real
declare @lon1 real
declare @lat2 real
declare @lon2 real

set @lat1 = 47.68 — Seattle
set @lon1 = -122.12
set @lat2 = 40.76 — New York
set @lon2 = -73.98

select dbo.DistanceBetween(@lat1, @lon1, @lat2, @lon2) as dist
go

The result was 3847.653 (km). The exact value of the function result depends on the constant used for the radius of the earth in kilometers.