Q:

Find rows with lat/long up to 500 meters away from a specific point

I have a table with Latitude and Longitude columns (both are float), and now I'm building a stored procedure that gets a point as a parameter and finds which rows are up to 500 meters away from it. 
I'm using the following statement on my where clause but it is not working:
(geometry::STGeomFromText('POINT(' + CAST(Longitude AS VARCHAR(20)) + ' ' + 
  CAST(Latitude AS VARCHAR(20)) 
  + ')', 4326).STDistance(@currentLocation) / 1000) < @radius

@currentLocation is geometry and @radius is float. 
I'm using SQL Server 2012.  What is wrong with my where clause? 

A:

There's several issues from a style and performance point of view, but in terms of functionality, the reason why the query is not working is because (I'm guessing) you're providing the @radius parameter of 500, expecting it to search for locations within 500 metres? 
However, Longitude and Latitude coordinates are angular coordinates, measured in degrees, right?  So what you're actually doing is finding those points that are less than 500/1000 degrees away from @currentLocation (not sure why you're dividing by 1000?) 
I'm guessing that you really wanted to use the geography datatype for this query.  You should also use the Point() method rather than the STGeomFromText() method, which will be slightly quicker and a lot tidier because it doesn't involve all that CASTing.  
Your WHERE clause would then look like this:
geography::Point(Latitude, Longitude, 4326).STDistance(@currentLocation) < @radius

There's still issues here - because you're only creating the geography Point instance dynamically for each row in the query, you won't be able to make use of any indexes, and the query will likely be quite slow.  A better approach would be to create a PERSISTED computed column in the table:
ALTER TABLE yourTable
ADD Location AS geography::Point(Latitude, Longitude, 4326) PERSISTED;

Then add a spatial index to this column (you'll need a clustered primary key on your table) and then your query just becomes:
SELECT * FROM yourTable
WHERE Location. STDistance(@currentLocation) < 500;

