MYSQL - table not updating from Procedure

Question

I want to get distance between two GeoPoints (using LatLong) for that I wrote GETDISTANCE function from solution provided [MySQL Function to calculate distance between two latitudes and longitudes. If I call function independently it works like charm. As per my understanding I cannot return ResultSet from Function in MySQL so I created Procedure and called function inside procedure As follows:

  DELIMITER $$
     CREATE PROCEDURE GetNearByGeoPoints(IN Lat REAL, IN Longi REAL)
       BEGIN
       DECLARE v_max int;
       DECLARE v_counter int unsigned default 0;


       SET @v_max = (SELECT COUNT(*) FROM TransmitterPointsData);

       START TRANSACTION;

       WHILE v_counter < v_max
       DO

       SELECT @coverageID :=CoverageID, @tableLatitude := Latitude, @tableLongitude :=Longitude FROM TransmitterPointsData LIMIT v_counter,1;

       SET @Dist= GETDISTANCE(Lat, Longi, tableLatitude, tableLongitude);

       UPDATE TransmitterPointsData SET DynamicDistance = @Dist WHERE CoverageID= @coverageID;

       set v_counter=v_counter+1;

       END WHILE;
       COMMIT;

       SELECT * FROM TransmitterPointsData;
       END $$
     DELIMITER ;

What I am trying to do is taking a set of LatLong parameters from user and comparing it with each set of LatLong from table. And after getting output from function I am updating TransmitterPointsData table with where condition on coverageID.

This is my first MySQL query so far I was following syntax but I do not know why I am getting all null values in DynammicDistance Column.

Thank You in Advance

Answer 1

Try replacing the while loop with this:

UPDATE TransmitterPointsData
SET DynamicDistance = GETDISTANCE(Lat, Longi, Latitude, Longitude)

Much shorter, and you avoid potential issues with row selection via limit + offset (which is poor style at best, and gives you a random row each time at worse).