Create PROCEDURE [SearchViaLocation]
@Longitude nvarchar(50)='0'
,@Latitude
nvarchar(50)='0'
,@Distance
decimal(18,8)=2000---(2km)
AS
BEGIN
-- SET NOCOUNT ON added to prevent
extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT
ON;
DECLARE @CurrentLocation geography;
SET @CurrentLocation
= geography::Point(@longitude, @latitude, 4326)
SELECT @CurrentLocation
SELECT * FROM YourTblName
WHERE geography::STPointFromText('POINT(' + CAST(ISNULL([LongitudeColumnName],'0') AS VARCHAR(20)) + ' ' +
CAST(ISNULL([LatitudeColumnName],'0') AS VARCHAR(20)) + ')', 4326).STDistance(@CurrentLocation )<= @Distance
In Above query you need to change 'YourTblName' with your table name,
'LongitudeColumnName' with your column which stores longitude and
'LatitudeColumnName' with your column which stores latitude.