Stored Procedure Ignoring DELIMITER change?

Hi All -

I'm running into some problems trying to import a Stored Procedure into a database running on my local MAMP server.

This is the procedure in question:

    DELIMITER $$
CREATE  PROCEDURE `ZipRangeFinder`(    
   zipbase VARCHAR(15),
   rangex  NUMERIC (15),
   inter NUMERIC(15),
   start NUMERIC(15),
   rows NUMERIC(15)
)
BEGIN
 DECLARE  lat1  DECIMAL (9,6); 
 DECLARE  long1  DECIMAL (9,6); 
 DECLARE  Maxlat1  DECIMAL (9,6); 
 DECLARE  Maxlon1  DECIMAL (9,6); 
 DECLARE  Minlat1  DECIMAL (9,6); 
 DECLARE  Minlon1  DECIMAL (9,6); 
 DECLARE  rangexFactor  DECIMAL (7,6);
 DECLARE  zipbaseint INT;
 DECLARE  lowrangex INT;

 SET rangexFactor = 0.014457;
 SET zipbaseint = zipbase;
 SET lowrangex = rangex-inter;

 SELECT *  INTO  lat1, long1 FROM (
  ( SELECT `zip_lat`, `zip_long`
   FROM  `a_zipcodes`
   WHERE  `zip_code` = CONVERT(zipbase USING latin1) COLLATE latin1_general_ci
  ) UNION (SELECT `zip_lat`, `zip_long`
   FROM  `a_zipcodes`
   WHERE  `zip_code` = zipbaseint
  )
 ) AS `DV1`
 LIMIT 0,1;

 SET Maxlat1 = lat1-(rangex*rangexFactor);
 SET Minlat1 = lat1+(rangex*rangexFactor);

 SET Maxlon1 = long1-(rangex*rangexFactor);
 SET Minlon1 = long1+(rangex*rangexFactor);

 SET @sql = CONCAT('
  SELECT * FROM (
   ( SELECT `loc_id`, `zip_code`, `zip_lat`, `zip_long`, `zip_dist` 
   FROM (
    SELECT `zip_code`, `zip_lat`, `zip_long`, `zip_dist`
    FROM (
     SELECT `zip_code`, `zip_lat`, `zip_long`, GetDistance(',lat1,', ',long1,', `zip_lat`, `zip_long`) AS `zip_dist`
                   FROM `a_zipcodes`
          WHERE `zip_lat` BETWEEN ',Maxlat1,' AND ',Minlat1,'
      AND `zip_long` BETWEEN ',Maxlon1,' AND ',Minlon1,'
          GROUP BY `zip_code`
    ) AS `a_zipcodes`
    WHERE `zip_dist` BETWEEN ',lowrangex,' AND ',rangex,'
    ORDER BY `zip_dist` ASC)  AS `a_zipcodes`
    INNER JOIN (SELECT `loc_id`, `loc_zip`
     FROM `loc_location`
     WHERE `loc_zip` != \'0\'
    ) AS `loc_location`
     ON `loc_location`.`loc_zip` = `zip_code`
   )
   UNION ALL
   ( SELECT `loc_id`, `zip_code`, `zip_lat`, `zip_long`, `zip_dist`
   FROM (
    SELECT `zip_code`, `zip_lat`, `zip_long`, `zip_dist`
    FROM (
     SELECT `zip_code`, `zip_lat`, `zip_long`, GetDistance(',lat1,', ',long1,', `zip_lat`, `zip_long`) AS `zip_dist`
                        FROM `a_zipcodes`
         INNER JOIN `a_states`
      ON `a_states`.`state_id` = `a_zipcodes`.`state_id`
         WHERE `zip_lat` BETWEEN ',Maxlat1,' AND ',Minlat1,'
      AND `zip_long` BETWEEN ',Maxlon1,' AND ',Minlon1,'
      AND `a_states`.`country_id` != \'38\' 
      AND `a_states`.`country_id` != \'109\'
         GROUP BY `zip_code`
    ) AS `a_zipcodes`
    WHERE `zip_dist` BETWEEN ',lowrangex,' AND ',rangex,'
    ORDER BY `zip_dist` ASC)  AS `a_zipcodes`
    INNER JOIN (SELECT `loc_id`, CONVERT(`loc_zip`, SIGNED) AS `loc_zip` 
     FROM `loc_location`
     WHERE `loc_zip` != \'0\'
    ) AS `loc_location`
     ON `loc_location`.`loc_zip` = `zip_code`
   )
  )  AS `DV1`
  GROUP BY `loc_id`
  ORDER BY `zip_dist` ASC
  LIMIT ',start,', ',rows,';');

 PREPARE stmt FROM @sql; EXECUTE stmt;

END$$

But MySQL seems to start trying to execute the various SQL statements inside of the procedure instead of storing it. Anyone know what I'm doing wrong?

Thanks!

ansaurus

tags:

views:

answers:

Stored Procedure Ignoring DELIMITER change?

related questions