parameterized sql query for zipcode and radius not working

Question

0.00/5 (No votes)

See more:

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:

SQL

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

longitude function:

SQL

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:

SQL

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:

SQL

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

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

Posted 3-Jul-13 17:56pm

memberxxxxxxxxxxxxxxxxx

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2013-07-03T20:23:00

Solution 1

For starters, did you mean to write

SQL

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

Or

SQL

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

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!

Posted 3-Jul-13 20:23pm

OriginalGriff

Comments

memberxxxxxxxxxxxxxxxxx 4-Jul-13 3:38am

I was trying anything and everything i could to try to make it work. i tried the first one, not the second. it didn't work. my latest attempt was:

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

and that didn't work.

i'm using vb web developer and trying all this out in the "new query" wizard. i'm expecting the query to ask me for a value for the distance, but it's not and simply returning an empty set. ie no rows, i know very little about sql so i don't want to get the terminology wrong.

i've left off the zipcodes part because that was another source of frustration. i've tried declaring it and replacing the '20013' with @zipcode, but i kept getting an error saying that i had to declare the scalar variable even though i declared it right under the declare for the lat and longitude

OriginalGriff 4-Jul-13 4:18am

SQL will not ask you for info. It will just complain when you don't provide it. Remember that SQL Server is not intended to interface with users directly - it expects to be the "back end" and interface with your application, which interfaces with the user.

What are you trying to achieve here? Because I can't help thinking you have gone down the wrong road! :laugh:

memberxxxxxxxxxxxxxxxxx 4-Jul-13 5:14am

lol it wouldn't surprise me. basically, i have a table of people, one of the columns has their zipcode. that zipcode is tied to a zipcodes table that has the zipcode, city, state, latitude and longitude. i'd like to add a filter that retrieves people within a radius of a specified zipcode, but i'd like to be able to put in the radius and the zipcode.

so i found those functions and tried incorporating them in my site, but i don't know much about sql and ran into the wall.

like i said, i'm using vb web developer. there's a query designer that sort of allows me to test queries and input whatever for variables, but when i try to run the query, nothing happens, just returns the columns, but no data. if i take out the @Distance and replace it with a number...it works.

this is where i got the function:
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-zipcode-latitude-longitude-pr

i tried modifying the sample query for stores lol. not successfully though.

OriginalGriff 4-Jul-13 5:33am

So create a Function or Stored Procedure that takes two parameters: @Zipcode and @Distance: it then looks a lot like the code the site provided you, but it accepts the zip and distance as parameters, and returns the SELECT List. You can't do it directly in SQL as a query - because a query is self contained where an SP expects parameters. (It's also easier to use in your VB code than a big query each time)

memberxxxxxxxxxxxxxxxxx 6-Jul-13 15:15pm

thanks i appreciate the help. worked out just as you said. just needed to run it in a stored procedure

OriginalGriff 6-Jul-13 15:33pm

You're welcome!