.
declare @latitude float, @longitude float, @distance float, @CountryCode varchar(50) select @distance = 1000 SET @latitude = (SELECT TOP 1 Lat FROM [cartrawler_v2].[dbo].[Location] where CartrawlerCode = 4867); SET @longitude = (SELECT TOP 1 Lng FROM [cartrawler_v2].[dbo].[Location] where CartrawlerCode = 4867); SET @CountryCode = (SELECT TOP 1 CountryCode FROM [cartrawler_v2].[dbo].[Location] where CartrawlerCode = 4867); ; with RestroomLocationsWithDistance as ( select Id, Lat, Lng, Name, address, CountryCode, ( 3959 * acos(cos(radians(@latitude)) * cos(radians( [Lat])) * cos(radians( [Lng]) - radians(@longitude) ) +sin(radians(@latitude)) * sin(radians( [Lat]) ) ) ) As Distance FROM [cartrawler_v2].[dbo].[Location] where CountryCode = @CountryCode and Lat<>@latitude and Lng<> @longitude) select TOP 10 Id,Lat,Lng, Name, Address,CountryCode,Distance,@latitude as citylat, @longitude as citylng from RestroomLocationsWithDistance WHERE Distance <= @distance and Name<> '' order by Distance SELECT TOP 1 Id,Lat,Lng, Name , Address,CountryCode,'0' as Distance, Lat as citylat, Lng as citylng FROM [cartrawler_v2].[dbo].[Location] where CartrawlerCode = 4867;