title: Haversine Formula works as Normal SQL but not in stored procedure
body: <p>I am using haversine formula to find distance between users. It works well as a normal mysql piece of code. But when use the same in a stored procedure it not working and throws 0.00 distances. I don't know why?</p>
  
  <p>This is the working code:</p>
  
  <pre><code>DROP TEMPORARY TABLE  if exists temp_tab;
      CREATE TEMPORARY TABLE temp_tab(temp_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
      user_fb_id bigint(20) unique,distance double(15,8) default NULL) CHARSET=utf8;
  
  INSERT into temp_tab (user_fb_id, distance)
  SELECT * 
  FROM (SELECT user_fb_id, 
               6371 * ACOS(SIN(RADIANS( 11.01684450 )) * SIN(RADIANS(`Latitude`)) + COS(RADIANS( 11.01684450 )) * COS(RADIANS(`Latitude`)) * COS(RADIANS(`Longitude`) - RADIANS( 76.95583210 ))) AS `distance` 
        FROM `user_login_log` 
        WHERE user_id &lt;&gt;'1831820984' 
        HAVING `distance` &lt;= 50 
        ORDER BY `activity_at` DESC) as t1
  GROUP BY user_fb_id;
  
  SELECT * FROM temp_tab;
  </code></pre>
  
  <p>Normal SQL output:
  <img src="https://i.stack.imgur.com/wWDbm.png" alt="Normal SQL output"></p>
  
  <p>Stored Procedure output:
  <img src="https://i.stack.imgur.com/wzcQn.png" alt="Stored Procedure output"></p>
  
  <p>Stored Procedure:</p>
  
  <pre><code>DELIMITER ;;
  CREATE DEFINER=`up_beta`@`127.0.0.1` PROCEDURE `usp_geo`(user_id bigint(20),latitude double(15,8),longitude double(15,8), dist int(100),location longtext,page int(2),page_size int(2))
  BEGIN
  
      Declare limitStart int(10);
  
      Declare limitEnd int(10);
  
  
      SET @user_id=(SELECT user_id);
  
      set @gen=(select gender from users where user_id=@user_id);
  
      set @latitude = (SELECT latitude);
  
      set @longitude = (SELECT longitude);
  
      set @dist = (SELECT dist);
  
      SET @location=(SELECT location);
  
      set limitStart =  ((page*page_size)-page_size);
  
      set limitEnd =  page_size ;
  
      set @SNO = ((page - 1)*page_size);
  
      IF @gen='male' then
          set @gen='female';
      else
          set @gen='male';
      end if;
  
  
      DROP TEMPORARY TABLE  if exists temp_tab;
      CREATE TEMPORARY TABLE temp_tab(temp_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
      user_id bigint(20) unique,distance double(15,8) default NULL) CHARSET=utf8;
  
  INSERT into temp_tab (user_id, distance)
  SELECT * FROM (SELECT user_id, 6371 * ACOS(SIN(RADIANS( @latitude )) * SIN(RADIANS(Latitude)) + COS(RADIANS( @latitude )) * COS(RADIANS(Latitude)) * COS(RADIANS(Longitude) - RADIANS( @longitude ))) AS `distance` FROM `user_log` WHERE user_id &lt;&gt;@user_id HAVING `distance` &lt;= @dist ORDER BY `activity_at` DESC) as t1
  GROUP BY user_id;
  
  
  
  SELECT * FROM temp_tab;
  
  DROP TEMPORARY TABLE  if exists search_tab;
      CREATE TEMPORARY TABLE search_tab(temp_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
      user_id bigint(20) unique,name varchar(500),gender varchar(100),
      town longtext,current_location longtext default NULL,is_in_app tinyint(4) default 0,distance double(15,8)) CHARSET=utf8;
  
  INSERT INTO search_tab(user_id,name,gender,town,distance) 
  SELECT a.user_id, a.name,a.gender,a.home_town,b.distance FROM users a, temp_tab b WHERE a.user_id = b.user_id AND a.gender=@gen AND a.relationship_id NOT IN(2,3,4);
  
  
   if @location&lt;&gt;'' AND @location&lt;&gt;NULL then
          begin
              set @city=(select concat('''','%',@location,'%',''''));
  
              set @gender =(select concat('''',@gen,''''));
  
             set @insert_sql = concat('INSERT IGNORE INTO search_tab(user_id,name,gender,town,location)
  SELECT a.user_id, a.name,a.gender,a.home_town,b.current_location FROM users a, user_details b WHERE b.current_location LIKE ',@city,' AND a.id = b.user_id AND a.gender=',@gender,' AND a.relationship_id NOT IN(2,3,4)');
  
              PREPARE stmt1 FROM @insert_sql;
              EXECUTE stmt1;
  
          end;
   end if;
  
  
   update search_tab  set is_in_app=1 where user_fb_id in
      (select user_id from users where access_token is not null);
  
  
  
      select  @a:=@a+1 sno,user_id,name,gender,town,current_location,is_in_app,distance
      from search_tab ,(SELECT @a:= @SNO) AS a
      limit limitStart,limitEnd;
  
      select count(temp_id)as total_count from search_tab; 
  
  end;;
  DELIMITER ;
  </code></pre>