Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Combining a query and a function
Message
From
13/04/2010 16:10:00
 
 
To
All
General information
Forum:
Microsoft SQL Server
Category:
Other
Title:
Combining a query and a function
Environment versions
SQL Server:
SQL Server 2008
Application:
Web
Miscellaneous
Thread ID:
01460047
Message ID:
01460047
Views:
141
Is it possible to combine a query with a function into one SQL command?

I have this query:
declare @CenterLat float=47.7795
declare @CenterLon float=-65.7191
declare @SearchDistance float=250

select * from test
 where (latitude>=@CenterLat - @SearchDistance/111.0 and latitude<=@CenterLat + @SearchDistance/111.0) and
 (longitude>=@CenterLon - @SearchDistance/111.0 and longitude<=@CenterLon + @SearchDistance/111.0)
 and dbo.CalculateDistance(@CenterLon, @CenterLat, Longitude, Latitude) <=@SearchDistance
I have this function:
ALTER FUNCTION [dbo].[CalculateDistance]
    (@Longitude1 DECIMAL(8,5), 
    @Latitude1   DECIMAL(8,5),
    @Longitude2  DECIMAL(8,5),
    @Latitude2   DECIMAL(8,5))
RETURNS FLOAT
AS
BEGIN
DECLARE @Temp FLOAT
 
SET @Temp = SIN(@Latitude1/57.2957795130823) * SIN(@Latitude2/57.2957795130823) + 
 COS(@Latitude1/57.2957795130823) * COS(@Latitude2/57.2957795130823) * COS(@Longitude2/57.2957795130823 -
 @Longitude1/57.2957795130823)
 
IF @Temp > 1 
    SET @Temp = 1
ELSE IF @Temp < -1
    SET @Temp = -1
 
RETURN (3958.75586574 * ACOS(@Temp) ) 
If it is possible, what kind of syntax can I use to combine all this into one SQL command?
Michel Fournier
Level Extreme Inc.
Designer, architect, owner of the Level Extreme Platform
Subscribe to the site at https://www.levelextreme.com/Home/DataEntry?Activator=55&NoStore=303
Subscription benefits https://www.levelextreme.com/Home/ViewPage?Activator=7&ID=52
Next
Reply
Map
View

Click here to load this message in the networking platform