T-SQL: Calculate distance in KM between 2 points

If you have 2 points (Lang and Lat) and you want to calculate distance between them in KM using SQL function, you can follow the following steps:

1. Execute the following script to declare a function to convert Degree to Radius:

CREATE FUNCTION [dbo].[ConvertDegreeToRadius](@Degree float)
RETURNS float
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN (
    SELECT @Degree * (PI() / 180)
)
END


2. Execute the following script to declare a function to calculate the distance in KM:

CREATE FUNCTION [dbo].[CalculateDistance](@firstLat float, @firstLang float, @secondLat float, @secondLang float)
RETURNS float
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN

-- static value, it is earch radius
DECLARE @EarchRadius int = 6371
DECLARE @Lat float = SearchTables.ConvertDegreeToRadius(@secondLat - @firstLat)
DECLARE @Lang float = SearchTables.ConvertDegreeToRadius(@secondLang - @firstLang)

DECLARE @a float = ( SIN(@Lat / 2) * SIN( @Lat / 2) + COS(SearchTables.ConvertDegreeToRadius(@firstLat)) * COS(SearchTables.ConvertDegreeToRadius(@secondLat)) * SIN (@Lang / 2) * SIN(@Lang / 2))
DECLARE @c float = 2 * ATN2(SQRT(@a), SQRT(1 - @a))
DECLARE @d float = @EarchRadius * @c

RETURN (
   

SELECT @d

)
END


3. Now, let us try to test the function and see the result: