title: Error Code: 1137 Can't reopen table: 'amountforagents'
body: <p>Please check my SP below </p>
  
  <pre><code>  DELIMITER $$
  
       CREATE DEFINER=`ntc`@`%` PROCEDURE `new_procedure`(in v_Agentid int)
  BEGIN
  
   select (select   @DayAmount :=sum(AmountRecevied) as Totoalamountperday from 
   collection_master  
  where  AgentID=v_Agentid and  day(Date_Time)= day(CURRENT_DATE()) ),
  
  
   (select  @MonthAmount:=sum(AmountRecevied) as Totoalamountperday  from 
  collection_master
  where  AgentID=v_Agentid and date_time between DATE_FORMAT(NOW() ,'%Y-%m-01') and LAST_DAY(now() - interval 0 month )),
  
  
  
  (select  @YearAmount:= sum(AmountRecevied) as Totoalamountpermonth  from 
  collection_master  
  where AgentID=v_Agentid  and year(Date_Time) =YEAR(CURRENT_DATE())),
  
   (select @Position := @Position + 1 AS Rank  from 
      collection_master ,(SELECT @Position := 0) r
  where AgentID=v_Agentid  
  group by AgentID
  ) as position;
  DROP TEMPORARY TABLE IF EXISTS amountforagents;
  
  create TEMPORARY table   amountforagents (agentId int,DayAmount decimal,MonthAmount  decimal,YearAmount decimal,Position int,totalamountreceived decimal);
     set @agentId =v_Agentid;
    update amountforagents SET totalamountreceived = (select ifnull(DayAmount,0)+ifnull(MonthAmount,0)+ifnull(YearAmount,0)  from amountforagents where agentId=v_Agentid);
  
   INSERT Into  amountforagents     
      (agentId,DayAmount,MonthAmount,YearAmount,Position,totalamountreceived) values(@agentId, 
  @DayAmount,@MonthAmount,@YearAmount,@Position,@totalamountreceived);
  
  
   select agentId,DayAmount,MonthAmount,YearAmount,Position,totalamountreceived from amountforagents;
  
  
   END
  </code></pre>
  
  <p>Here I am triing to give order by for totalamountreceived  to assign rank based on total amount of three columns as in sp,those three columns are in temp table.but it's showing error Error Code: 1137 Can't reopen table: 'amountforagents'</p>