I'm trying to create stored procedure, but after reading mysql's online document, I was not able to comprehend its usage. Here is what I do, put in target zip code and miles range, then find a list of zipcode, city, state and miles from target zip code. How do I get around to it?
[code] SET @targetzip='19943'; /*Establish the starting zip code.(Domain)*/ SET @rangemiles='20'; /*Specify the miles range from that starting zip code.(Range)*/ SELECT C.ZipCode, Zi.City, Zi.State, Round(C.Miles) AS 'Mile(s)' FROM ( SELECT Lat_A, Long_A, Lat_B, Long_B, ZipCode, (degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) + cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A - long_B)))))) * 69.09 AS 'Miles' FROM ( SELECT CAST(latitude AS decimal(8,6)) AS Lat_A, CAST(longitude AS decimal(8,6)) AS Long_A FROM zips WHERE `zip [EMAIL PROTECTED] ) AS A, ( SELECT CAST(latitude AS decimal(8,6)) AS Lat_B, CAST(longitude AS decimal(8,6)) AS Long_B, `zip code` AS ZipCode FROM zips ) AS B WHERE (degrees(acos((sin(radians(lat_A)) * sin(radians(lat_B)) + cos(radians(lat_A)) * cos(radians(lat_B)) * cos(radians(long_A - long_B)))))) * 69.09 <= @rangemiles ) AS C, zips Zi WHERE Zi.`zip code`=C.ZipCode ORDER BY Round(C.Miles); /* RESULT +---------+----------------+----------+----------------+ | ZipCode | City | State | Round( C.Miles) | +---------+----------------+----------+----------------+ | 19943 | Felton | Delaware | 0 | | 19979 | Viola | Delaware | 3 | | 19980 | Woodside | Delaware | 4 | | 19934 | Camden Wyoming | Delaware | 6 | | 19962 | Magnolia | Delaware | 6 | | 19946 | Frederica | Delaware | 7 | | 19952 | Harrington | Delaware | 7 | | 19954 | Houston | Delaware | 8 | | 19964 | Marydel | Delaware | 10 | | 19942 | Farmington | Delaware | 10 | | 19963 | Milford | Delaware | 11 | | 19901 | Dover | Delaware | 11 | | 19902 | Dover Afb | Delaware | 11 | | 19904 | Dover | Delaware | 11 | | 19953 | Hartly | Delaware | 11 | | 21636 | Goldsboro | Maryland | 11 | | 21640 | Henderson | Maryland | 12 | | 21649 | Marydel | Maryland | 12 | | 21639 | Greensboro | Maryland | 12 | | 19950 | Greenwood | Delaware | 13 | | 19955 | Kenton | Delaware | 15 | | 19960 | Lincoln | Delaware | 15 | | 21644 | Ingleside | Maryland | 16 | | 21660 | Ridgely | Maryland | 16 | | 21629 | Denton | Maryland | 16 | | 21607 | Barclay | Maryland | 17 | | 19941 | Ellendale | Delaware | 17 | | 19938 | Clayton | Delaware | 18 | | 19933 | Bridgeville | Delaware | 19 | | 21668 | Sudlersville | Maryland | 19 | | 21641 | Hillsboro | Maryland | 20 | | 19977 | Smyrna | Delaware | 20 | +---------+----------------+----------+----------------+ */ [/code]