CODEWITHSUNDEEP
mysqlsqlstored-procedurescursormysql-workbench
devon123
devon123

Reputation: 21

Variable or condition declaration after cursor or handler declaration SQL Statement (mysql)

even though i tried many things it still gives me this error (Variable or condition declaration after cursor or handler declaration SQL Statement)

the sp must use cursors

CREATE PROCEDURE monthly_sum (IN cardId INT(36), IN monthN INT, OUT sumV float)
BEGIN
DECLARE cursor_1 CURSOR FOR
SELECT SUM(purchased.amountPurchased)
FROM purchased
WHERE MONTH(purchased.dateUsed) = monthN AND purchased.purchasedCardId = cardId AND DAY(purchased.dateUsed) <=10;

DECLARE cursor_2 CURSOR FOR
SELECT SUM(purchased.amountPurchased)
FROM purchased
WHERE MONTH(purchased.dateUsed)  = monthN AND purchased.purchasedCardId = cardId AND DAY(purchased.dateUsed) <=20 AND DAY(purchased.dateUsed) >10;

DECLARE cursor_3 CURSOR FOR
SELECT SUM(purchased.amountPurchased)
FROM purchased
WHERE MONTH(purchased.dateUsed) = monthN AND purchased.purchasedCardId = cardId AND DAY(purchased.dateUsed) >20;

DECLARE percentage1, percentage2, percentage3 float default 0;

OPEN cursor_1;
FETCH cursor_1
INTO percentage1;
CLOSE cursor_1;

OPEN cursor_2;
FETCH cursor_2
INTO percentage2;
CLOSE cursor_2;

OPEN cursor_3;
FETCH cursor_3
INTO percentage3;
CLOSE cursor_3;
SET sumV = percentage1*0.01 + percentage2*0.02 + percentage1*0.03;

SELECT sumV;
END

expected output the sum of 1% of first 10 days, 2% next 10 days, 3% the rest days of the month

Upvotes: 1

Views: 1718

Answers (2)

James
James

Reputation: 1829

Instead of calling cursor unnecessarily you can use select into. Since cursor is used to fetch row by row but in your case it results only once.

CREATE PROCEDURE monthly_sum (IN cardId INT(36), IN monthN INT, OUT sumV float)
BEGIN

DECLARE percentage1, percentage2, percentage3 float default 0;

SELECT SUM(purchased.amountPurchased) INTO percentage1
FROM purchased
WHERE MONTH(purchased.dateUsed) = monthN 
AND purchased.purchasedCardId = cardId 
AND DAY(purchased.dateUsed) <=10;

SELECT SUM(purchased.amountPurchased) INTO percentage2
FROM purchased
WHERE MONTH(purchased.dateUsed)  = monthN 
AND purchased.purchasedCardId = cardId 
AND DAY(purchased.dateUsed) <=20 AND DAY(purchased.dateUsed) >10;

SELECT SUM(purchased.amountPurchased) INTO percentage3
FROM purchased
WHERE MONTH(purchased.dateUsed) = monthN 
AND purchased.purchasedCardId = cardId 
AND DAY(purchased.dateUsed) >20;

SET sumV = percentage1*0.01 + percentage2*0.02 + percentage1*0.03; # I think percentage3 to be added instead of percentage1
END

Upvotes: 0

devon123
devon123

Reputation: 21

i added DECLARE percentage1, percentage2, percentage3 float default 0; at the beginning like karmens89 comment me and it worked

Upvotes: 1

Related Questions