zipcode和radius的参数化sql查询无法正常工作 [英] parameterized sql query for zipcode and radius not working

问题描述

我有一张桌子



有列邮政编码的人



与a有关zipcodes表，其中包含zip码作为id和纬度和经度列。



我发现了一系列计算球体距离的函数（基本上用于zipcodes）使用纬度和经度。我是sql的新手，想知道如何在存储过程中使用这些函数。



纬度函数：

I have a table

persons that has a column zipcode

that is related to a zipcodes table that has the zipcode as the id and a column for latitudes and longitudes.

I found a series of functions that calculate distance on a sphere (basically for zipcodes) using latitude and longitude. I'm rather new to sql and was wondering how to use these functions in a stored procedure.

latitude function:

ALTER Function [dbo].[LatitudePlusDistance](@StartLatitude Float, @Distance Float) Returns Float
    As
    Begin
        Return (Select @StartLatitude + Sqrt(@Distance * @Distance / 4766.8999155991))
    End

经度函数：

longitude function:

ALTER FUNCTION [dbo].[LongitudePlusDistance]
	(
		@StartLongitude float,
		@StartLatitude float,
		@Distance float
	)
RETURNS Float
AS
	begin

	RETURN (select @startLongitude + sqrt(@Distance * @Distance/(4784.39411916406*Cos(2*@StartLatitude/114.591559026165)*Cos(2*@StartLatitude/114.591559026165))))
	END

计算得出：

calculatedistance:

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) )

   End

糟糕的查询尝试：

crappy query attempt:

Declare @Longitude Decimal(8,5)
    Declare @Latitude Decimal(8,5)
     
    Select  @Longitude = Longitude,
            @Latitude = Latitude
    From    ZipCodes
    Where   ZipCode = '20013'
     
Declare @Distance int

    Select  persons.personName, ZipCodes.City,  dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude) As Distance
    From    persons
            Inner Join ZipCodes
                On persons.zipcode = ZipCodes.ZipCode
    Order By dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude)
WHERE dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude) As Distance <= @Distance

我试过这只是为了过滤参数化搜索范围之外的人而且它不起作用。说关键字附近有不正确的语法，其中



甚至不包括我想要输入邮政编码的参数，而不是：



其中zipcode ='20013'



我喜欢这样的东西：



其中zipcode = @zipcode



但它说我需要声明zscalar变量@zipcode并且无论我在哪里尝试做那...我一直得到同样的错误

I tried this just to filter the persons outside a parameterized search radius and it doesn't work. says there's an "incorrect syntax near the keyword "where"

that doesn't even include my desire to throw in a parameter for the zipcode so instead of:

where zipcode = '20013'

i'd like something like:

where zipcode = @zipcode

but it says i need to declare the zscalar variable @zipcode and no matter where i try to do that...i keep getting the same error

推荐答案

对于初学者，你的意思是写

For starters, did you mean to write

WHERE dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude) As Distance <= @Distance

或

Or

WHERE dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude) And Distance <= @Distance

如果你想使用@ZipCode的参数化查询，然后你需要像任何其他变量一样通过DECLARE提供参数 - 遇到它不知道的变量的SQL不会提示你它的值！

If you want to use a parametrized query for @ZipCode, then you need to provide the parameter via a DECLARE just like any other variable - SQL meeting a variable it doesn't know won't prompt you for it's value!

这篇关于zipcode和radius的参数化sql查询无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！