Cacher is the code snippet organizer for pro developers

DECLARE @MetersPerMile FLOAT = 1609.344;
DECLARE @Meters INT = 1000;

-- KATHMANDU
DECLARE @LocStart GEOGRAPHY= geography::Point(27.7017, 85.3206, 4326);

-- POKHARA
DECLARE @LocDest GEOGRAPHY= geography::Point(28.2669, 83.9685, 4326);   

SELECT  'Kathmandu to Pokhara' [Route] ,
        @LocStart.STDistance(@LocDest) / @MetersPerMile [Distance In Miles];

-- OUTPUT
-- Route                | Distance In Miles
-- Kathmandu to Pokhara |	91.3553381926787


SELECT  'Kathmandu to Pokhara' [Route] ,
        @LocStart.STDistance(@LocDest) / @Meters [Distance In Kilometers];


-- OUTPUT
-- Route                | Distance In Kilometers
-- Kathmandu to Pokhara |	147.022165388358

--- CALCULATE DISTANCE FROM CURRENT LOCATION
--- SOURCE: https://www.codeproject.com/Tips/1185780/Calculate-Geo-Distance-Using-SQL-Server

--  LocationID | LocationName | Latitude | Longitude 
--  1          | Aastodia     | 23.0168  | 72.5909 
--  2          | Ambawadi     | 23.0262  | 72.5475 
--  3          | Bapunagar    | 23.0329  | 72.6284 
--  4          | Behrampura   | 23.0048  | 72.5840
--  5          | Bodakdev     | 23.0400  | 72.5130
--  6          | Bopal        | 23.0334  | 72.4672
--  7          | C.G. Road    | 23.0280  | 72.5577
--  8          | Chandkheda   | 23.1120  | 72.5766 
--  9          | Dariyapur    | 23.0436  | 72.5825
--  10         | Ellis Bridge | 23.0259  | 72.5663 

--- CREATE SAMPLE TABLE
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].[SampleLatitudeLongitude]
    (
      [LocationID] [BIGINT] IDENTITY(1, 1)
                            NOT NULL ,
      [LocationName] [NVARCHAR](100) NULL ,
      [Latitude] [NVARCHAR](100) NULL ,
      [Longitude] [NVARCHAR](100) NULL ,
      CONSTRAINT [PK_SampleLatitudeLongitude] PRIMARY KEY CLUSTERED
        ( [LocationID] ASC )
        WITH ( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF )
    );

GO
SET IDENTITY_INSERT [dbo].[SampleLatitudeLongitude] ON; 

GO
INSERT  [dbo].[SampleLatitudeLongitude]
        ( [LocationID], [LocationName], [Latitude], [Longitude] )
VALUES  ( 1, N' Aastodia    ', N'23.0168', N'72.5909' ),
        ( 2, N'Ambawadi     ', N'23.0262', N'72.5475' ),
        ( 3, N'Bapunagar    ', N'23.0329', N'72.6284' ),
        ( 4, N'Behrampura   ', N'23.0048', N'72.5840' ),
        ( 5, N'Bodakdev     ', N'23.0400', N'72.5130' ),
        ( 6, N'Bopal        ', N'23.0334', N'72.4672' ),
        ( 7, N'C.G. Road    ', N'23.0280', N'72.5577' ),
        ( 8, N'Chandkheda   ', N'23.1120', N'72.5766' ),
        ( 9, N'Dariyapur    ', N'23.0436', N'72.5825' ),
        ( 10, N' Ellis Bridge', N'23.0259', N'72.5663' );
GO
SET IDENTITY_INSERT [dbo].[SampleLatitudeLongitude] OFF;
GO
-------------------------------------------------------------

--- EXECUTE SP
DECLARE @GEO1 GEOGRAPHY ,
    @Latitude NVARCHAR(10) ,
    @Longitude NVARCHAR(10);

SET @Latitude = '23.012034';
SET @Longitude = '72.510754';

SET @GEO1 = geography::Point(@Latitude, @Longitude, 4326);

SELECT  LocationID ,
        LocationName ,
        ( @GEO1.STDistance(geography::Point(ISNULL(Latitude, 0),
                                            ISNULL(Longitude, 0), 4326)) ) AS Distance
FROM    dbo.SampleLatitudeLongitude;


SELECT  LocationID ,
        LocationName ,
        LEFT(CONVERT(VARCHAR, ( @GEO1.STDistance(geography::Point(ISNULL(Latitude,
                                                              0),
                                                              ISNULL(Longitude,
                                                              0), 4326))
                                / 1000 )), 5) + ' Km' AS Distance
FROM    dbo.SampleLatitudeLongitude;


--- OUTPUT 1: IN METERS

--  LocationID | LocationName | Distance
--  1	         | Aastodia     | 8232.83442455536
--  2	         | Ambawadi     | 4080.39751191023
--  3	         | Bapunagar    | 12278.7674212285
--  4	         | Behrampura   | 7551.51479019033
--  5	         | Bodakdev     | 3105.62216875605
--  6	         | Bopal        | 5052.78737609732
--  7	         | C.G. Road    | 5126.85960229462
--  8	         | Chandkheda   | 12964.9962783794
--  9	         | Dariyapur    | 8142.64816678894
--  10	       | Ellis Bridge | 5897.35023275367

--- OUTPUT 2: IN KILOMETER

--  LocationID | LocationName | Distance
--  1	       | Aastodia     |	8.232 Km
--  2	       | Ambawadi     |	4.080 Km
--  3	       | Bapunagar    |	12.27 Km
--  4	       | Behrampura   |	7.551 Km
--  5	       | Bodakdev     |	3.105 Km
--  6	       | Bopal        |	5.052 Km
--  7	       | C.G. Road    |	5.126 Km
--  8	       | Chandkheda   |	12.96 Km
--  9	       | Dariyapur    |	8.142 Km
--  10	       | Ellis Bridge |	5.897 Km


-- STORED PROCEDURE

--- EXECUTE [dbo].[usp_GetNearestUsers] '23.012034','72.510754' ,5

CREATE PROCEDURE [dbo].[usp_GetNearestUsers]
    @Latitude NVARCHAR(10) ,
    @Longitude NVARCHAR(10) ,
    @DistanceInKM FLOAT
AS
    BEGIN

        DECLARE @GEO1 GEOGRAPHY;

        SET @GEO1 = geography::Point(@Latitude, @Longitude, 4326);

        SELECT  LocationID ,
                LTRIM(RTRIM(LocationName)) AS LocationName ,
                LEFT(CONVERT(VARCHAR, ( @GEO1.STDistance(geography::Point(ISNULL(Latitude,
                                                              0),
                                                              ISNULL(Longitude,
                                                              0), 4326)) )
                     / 1000), 5) + ' Km' AS Distance
        FROM    dbo.SampleLatitudeLongitude
        WHERE   ( @GEO1.STDistance(geography::Point(ISNULL(Latitude, 0),
                                                    ISNULL(Longitude, 0), 4326)) )
                / 1000 < @DistanceInKM;

    END;


--- NOTE:
--- 4326 is the SRID ( Spatial Reference ID )
--- 4326 is a World Geodetic System
--- Reference: https://en.wikipedia.org/wiki/World_Geodetic_System

--- Go Through The Following Table For Other SRID`s in SQL Server
SELECT *FROM sys.spatial_reference_systems WHERE spatial_reference_id=4326