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//
source to share
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).
source to share