title: Mysql use stored procedure results multiple times within a query
body: <p>i have a stored procedure in my MySQL db, working fine. </p>
  
  <p>However, since i have to call the same procedure 3 times in the same query with the same parameters, it woulb be a nice performance improvement if i could use the result of the first call instead of subsequent calls.</p>
  
  <p><strong>Edit: changed the example because it was oversimplified, misleading replies</strong></p>
  
  <p>I have to tables: products, and prices - prices change depending on the time of the year, and on payment mode,so structure is: (id, product_id, date_from, date_to, price_mode_1, price_mode_2, price_mode_3)</p>
  
  <p>Example:</p>
  
  <p>what i do now is</p>
  
  <pre><code>SELECT products.*, 
         get_a_quote(products.id,date_to,date_from, quantity_mode_1,quantity_mode_2,quantity_mode_3) 
    FROM peoducts 
   WHERE get_a_quote(products.id,date_to,date_from, quantity_mode_1,quantity_mode_2,quantity_mode_3)  &gt;0 
  ORDER BY 
         get_a_quote(products.id,date_to,date_from, quantity_mode_1,quantity_mode_2,quantity_mode_3) 
  LIMIT 10
  </code></pre>
  
  <p>what i want to do is </p>
  
  <pre><code>SELECT products.*, 
         get_a_quote(products.id,date_to,date_from, quantity_mode_1,quantity_mode_2,quantity_mode_3)  into quote
    FROM peoducts 
   WHERE quote  &gt;0 
  ORDER BY 
         quote
  LIMIT 10
  </code></pre>
  
  <p>Is this possible?</p>
  
  <p>*<em>Edit: function code *</em></p>
  
  <pre><code>FUNCTION `get_a_quote`(`productid` INT, `startDate` SMALLINT, `endDate` SMALLINT, `mode_1_quantity` SMALLINT, `mode_2_quantity` TINYINT, `mode_3_quantity` TINYINT) RETURNS smallint(6)
  BEGIN
  declare totalprice,p_1,p_2,p_3 smallint;
  SELECT price_1,price_2,price_3 into p_1,p_2,p_3 FROM pricing WHERE id=productid and ((startDate between date_from and date_to) and (endDate between date_from and date_to));
  if p_3 is null then
      set mode_2_quantity=mode_3_quantity+mode_2_quantity;
      set mode_1_quantity=mode_1_quantity+(4*mode_3_quantity);
      set p_3=0;
      set mode_3_quantity=0;
  end if;
  if p_2 is null then
      set mode_1_quantity=mode_1_quantity+(3*mode_2_quantity);
      set p_2=0;
      set mode_2_quantity=0;
  end if;
  if mode_1_quantity&gt;0 and p_1 is null then
      return 0;
  end if;
  set totalprice = p_1*mode_1_quantity+p_2*mode_2_quantity)+p_3*mode_3_quantity);
  RETURN totalprice;
  END
  </code></pre>
  
  <p>Thanks everybody</p>