Select Non-Returning Values ​​in MySQL Stored Procedure

I have a stored procedure shown below that I created to add dollar sales to a (WeeklySales) table that currently only stores sales units. The cursor works in the WeeklySales table. Pricing data is stored in a pricing table. The price table actually contains the changes in prices. The effective date of the price change is stored in Price.effectiveDate, so I need to find the price that was effective for the week the unit was sold in (which is stored in WeeklySales.weekStart).

The problem I'm running into is that the first choice after the IF returns nothing. I have confirmed that this select returns a value when I run it outside of the procedure using the values ​​that will be called inside the procedure. I'm not sure what is wrong here, but I'm guessing it might be because this selection is working on a table other than the cursor? Somebody knows? Is there a better way to do this?

DELIMITER //

CREATE PROCEDURE `createWeeklyPricing` (IN startDate DATE, IN endDate DATE)  
BEGIN
--
-- Populate the proceeds column using the Pricing table
DECLARE product VARCHAR(255);
DECLARE weekStart DATE;
DECLARE units, done INT;
DECLARE proceeds DECIMAL(6,2);
DECLARE effectiveDate DATE;
DECLARE currentRow CURSOR FOR SELECT `weekStart`, `product`, `units` FROM `WeeklySales` WHERE `weekStart` >= startDate AND `weekStart` <= endDate;  
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  
OPEN currentRow;  
SET done = 0;  
WHILE done = 0 DO  
    FETCH currentRow INTO weekStart, product, units;
    IF done = 0 THEN  
        SELECT MAX(`effectiveDate`) FROM `Pricing` WHERE `effectiveDate` <= weekStart AND `product` = product INTO effectiveDate;
        SELECT `proceeds` FROM `Pricing` WHERE `effectiveDate` = effectiveDate AND `product` = product INTO proceeds;
        UPDATE `WeeklySales` SET `proceeds` = units * proceeds WHERE `weekStart` = weekStart AND `product` = product;
    END IF;
END WHILE;  
CLOSE currentRow;

END//

      

+3


source to share


3 answers


echo (select) weekstart before if statement ...



If it returns zero change then select FROM WeeklySales

WHERE weekStart

between startDate AND endDate

0


source


you need to use INTO

before FROM

and variable needs sign'@'

change it to



SELECT MAX(`effectiveDate`) INTO @effectiveDate FROM `Pricing` WHERE `effectiveDate` <= weekStart AND `product` = product ;

      

hope this helps

0


source


This is because your variable name overwrites the column name:

You have a variable named 'effectiveDate'

You have a column named 'effectiveDate'

SELECT MAX(`effectiveDate`) ...

      

Is the MAX-ing variable effectiveDate, not a column

Try assigning to maxEffectiveDate variable

Beware that variables are case insensitive. It happened to me when I tried to select a column IsBackUp variable isBackUp (note i).

0


source







All Articles