Assigning function results to a variable in a MySql stored procedure-CodePudding

I am trying to write a stored procedure to iterate over a table of prices and a table of products. Where there is a price which matches an ID in the product table, the latest price with an effective date earlier than today should be applied to the 'currentPrice' column in the product table. Thereby the 'currentPrice' column holds the most up-to-date active price, and price changes can be scheduled by inserting into the price table with a future date.

At the moment I have this:

DELIMITER $$
 create procedure updatePrice()
 begin
 declare loopLeng int default 1000;
 declare loopMax int default 1099;
 declare newPrice decimal(10,2);
 --select min(idProduct) into loopLeng from product;
--select count(idProduct) into loopMax from product;
set loopLeng  = 1000;
set loopMax = 1099;
 updateLoop : LOOP
    if loopLeng > loopMax  then 
    leave updateLoop;
    end if;
    select price into newPrice from price where idProduct = loopLeng and dateApplicableFrom = (select max(dateApplicableFrom) from price where idProduct = loopLeng and dateApplicableFrom <= current_timestamp());
    update product set currentPrice = newPrice where idProduct = loopLeng;
    set loopLeng = loopLeng   1; 
    end loop;
    end
    $$ DELIMITER ;

This works fine... but obviously contains hardcoded values for loopLeng and loopMax (defining the size of the loop over product), so it isn't flexible if the number of products changes. I want to set these values dynamically based on the actual min of idProduct and count of product, like in the commented lines 7 & 8. This seems like it should work to me & does not give any errors, but whenever I execute the procedure with these definitions it fails to perform the necessary updates.

I have also tried creating tempoary variables, selecting the function results into those, and the assigning those values to loopLeng and loopMax, but this had the same result. So far I can only get this to execute as expected with hardcoded values. Can anybody suggest where I'm going wrong please?

CodePudding user response：

Try using prepared statement as follows :

set @sql = 'select count(idProduct) into loopMax from product';
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

set @sql = 'select count(idProduct) into loopMax from product;';
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CodePudding user response：

The solution was to use a prepared statement to assign the vslues as in SelVazi's answer