CODEWITHSUNDEEP
sqlsql-servernerddinner
raklos
raklos

Reputation: 28545

distancebetween sql function and order by closest

Im using this function to get events within 30 miles, its pretty much the same as the nerd dinner one. But How can I order them by 'closest'..?

ALTER FUNCTION [dbo].[NearestEvents] 
  ( 
  @lat real, 
  @long real 
  ) 
RETURNS  TABLE 
AS 
  RETURN 
  SELECT Events.ID 
  FROM   Events  
  WHERE  dbo.DistanceBetween(@lat, @long, Latitude, Longitude) <30

Upvotes: 1

Views: 381

Answers (1)

Yada
Yada

Reputation: 31225

Add it to your SELECT list.

SELECT Events.ID,
  dbo.DistanceBetween(@lat, @long, Latitude, Longitude) as distance
  FROM   Events  
  WHERE  dbo.DistanceBetween(@lat, @long, Latitude, Longitude) <30 
  ORDER BY distance

--Since it is still not working for OP a good way to debug stored procedure is by hijacking the parameters. Try this with your Query Analyser and see that you get:

DECLARE @lat = varchar(100);
DECLARE @long = vavhar(100);

SET @lat = 'XXXXX';
SET @long = 'XXXXX';

 SELECT Events.ID,
      dbo.DistanceBetween(@lat, @long, Latitude, Longitude) as distance
      FROM   Events  
      WHERE  dbo.DistanceBetween(@lat, @long, Latitude, Longitude) <30 
      ORDER BY distance;

Upvotes: 3

Related Questions