MySQL Forums
Forum List  »  Stored Procedures

mySQL stored procedure null values, but not all row
Posted by: Bucur Marius
Date: May 13, 2014 10:32AM

I have a problem with a stored procedure I wrote. The procedure is suppose to retrieve a result set from a table where I have the Latitude and Longitude then loop through the result with a cursor and update a second table. Trouble is that latitude and longitude remain at zero and no update is made. While running the raw_data query independent it gives the expected results with valid latitude and longitude, the procedure will not see them. What is weird is the fact that zip variable is correctly seen by the fetch.

Data types for variables are the same as in both tables involved.

While I know sql I am pretty green when working with stored procedures. Any hint will be greatly appreciated.

DELIMITER $$

CREATE PROCEDURE `updateLatLong` (state varchar(2))
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE latitude DECIMAL(9,6) DEFAULT 0;
DECLARE longitude DECIMAL(9,6) DEFAULT 0;
DECLARE zip CHAR(5) DEFAULT '00000';

DECLARE raw_data CURSOR FOR
SELECT SQL_NO_CACHE `Latitude`, `Longitude`, `ZIPCode`
FROM `zip5`
WHERE `StateAbbr`=state
ORDER BY ZIPCode ASC;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN raw_data;

read_loop: LOOP
FETCH FROM raw_data INTO latitude, longitude, zip;
/*SELECT state, zip, latitude, longitude;*/
IF done THEN
LEAVE read_loop;
END IF;
UPDATE `locations` SET `Latitude`=latitude, `Longitude`=longitude WHERE `StateAbbr`=state AND `ZIPCode`=zip;

END LOOP;
CLOSE raw_data;
END$$

DELIMITER ;

Options: ReplyQuote


Subject
Views
Written By
Posted
mySQL stored procedure null values, but not all row
3378
May 13, 2014 10:32AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.