CODEWITHSUNDEEP
sql-serverazure-sql-database
Mees Kluivers
Mees Kluivers

Reputation: 530

SQL Server User Defined Function

I am trying to create a UDF which returns a SELECT statement.

The function:

CREATE FUNCTION closestCity
     (@givenLat decimal(11, 7),
      @givenLong decimal(11, 7),
      @locLat decimal(11, 7),
      @locLong decimal(11, 7)
     )
RETURNS nvarchar(100)123
AS BEGIN
    DECLARE @city_name nvarchar(100)

    SELECT top 1 @city_name = name
    FROM 
        (SELECT 
             SQRT(POWER(69.1 * (@locLat - @givenLat), 2) +
                  POWER(69.1 * (@givenLong - @locLong) * COS(@locLat / 57.3), 2)) AS distance, 
             name 
         FROM city) d
    WHERE d.distance < 35
    ORDER BY d.distance

    RETURN @city_name
END;

However it does not return my expected result. When I run the query 'manually' with the same values it does return the expected result however.

I figured out that running the query without the ORDER BY has the same result as it has running the UDF as I posted it. So is the ORDER BY somehow being ignored or am I just looking over something?

I would call the function like so;

SELECT name 
FROM city 
WHERE dbo.closestCity([lat], [long], city.latitude, city.longitude) = city.name;

But as I said this returns something different than running;

SELECT TOP 1 
    name
FROM 
    (SELECT 
         SQRT(POWER(69.1 * (@locLat - @givenLat), 2) +
              POWER(69.1 * (@givenLong - @locLong) * COS(@locLat / 57.3), 2)) AS distance, 
         name 
     FROM city) d
WHERE 
    d.distance < 35
ORDER BY 
    d.distance

Where @givenLat and @givenLong are coordinates send as decimals and @locLat and @locLong are the coordinates stored in the database.

Upvotes: 0

Views: 393

Answers (1)

Ann L.
Ann L.

Reputation: 13965

Note: this is from memory. It hasn't been tested.

CREATE FUNCTION closestCity
 (@givenLat decimal(11, 7),
  @givenLong decimal(11, 7),
 )
RETURNS nvarchar(100)123
AS BEGIN
  DECLARE @city_name nvarchar(100)

SELECT top 1 @city_name = name
FROM 
    (SELECT 
         SQRT(POWER(69.1 * (city.latitude - @givenLat), 2) +
              POWER(69.1 * (@givenLong - city.longitude) * COS(city.latitude / 57.3), 2)) AS distance, 
         name 
     FROM city) d
WHERE d.distance < 35
ORDER BY d.distance

RETURN @city_name
END;

You'd call it like this:

 select name
 FROM city 
 WHERE dbo.closestCity(city.latitude, city.longitude) = city.name;

... although, actually, if all you want is the city name that's closest for each city, you could do this:

SELECT  NearestCity = dbo.closestCity(city.latitude, city.longitude)
    ,   GivenCity = city.name 
FROM city

Or, if you want just a single city name and have the lat and long for that city already, you could call it like this:

SELECT  dbo.closestCity(@givenLat, @givenLong)

Upvotes: 1

Related Questions