Stored Procedure for list_zip_by_miles

[LinuxGold]

I am trying to create a procedure that lists a lists of zip codes within given miles and local zip code.  After reading manual online I am unable to understand how to create it.  I use mysql command line interface to create stored procedure.  How do I get around to it?  I want to do list_zip_by_miles (19943,20) as following with result afterwards:

[code]
SET @targetzip='19943';  /*Local Zip Code*/
SET @rangemiles='20';    /*Mile(s) range limit */
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 code`=@targetzip
) 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);
[/code]
[code]
+---------+----------------+----------+---------+
| ZipCode | City          | State    | Mile(s) |
+---------+----------------+----------+---------+
| 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 |
| 19904  | Dover          | Delaware |      11 |
| 19901  | Dover          | Delaware |      11 |
| 19963  | Milford        | Delaware |      11 |
| 21636  | Goldsboro      | Maryland |      11 |
| 19902  | Dover Afb      | Delaware |      11 |
| 19953  | Hartly        | Delaware |      11 |
| 21640  | Henderson      | Maryland |      12 |
| 21639  | Greensboro    | Maryland |      12 |
| 21649  | Marydel        | 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 |
| 19941  | Ellendale      | Delaware |      17 |
| 21607  | Barclay        | Maryland |      17 |
| 19938  | Clayton        | Delaware |      18 |
| 19933  | Bridgeville    | Delaware |      19 |
| 21668  | Sudlersville  | Maryland |      19 |
| 21641  | Hillsboro      | Maryland |      20 |
| 19977  | Smyrna        | Delaware |      20 |
+---------+----------------+----------+---------+
[/code]

[LinuxGold]

Anyone know how to create stored procedure?  This is the bottleneck that I'm having right now.  Any url for rookie like me?

[fenway]

I haven't come across a really good tutorial/resource, mainly becuase I never use SPs -- but I don't see you ever resetting the delimiter.

[LinuxGold]

Ugh, delimiter got it.  *fixes my own eyes*
[code]
delimiter //
CREATE PROCEDURE find_zip_by_miles (
targetzip INT,
rangemiles INT
)
/*
*/
BEGIN
/* DECLARE @targetzip=int;
DECLARE @rangemiles int;*/
SET @targetzip=targetzip;
SET @rangemiles=rangemiles;
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 code`=@targetzip
) 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);
END
//
DELIMITER ;
CALL find_zip_by_miles (19943,20);
[/code]