Loop is not valid at this position expecting if-CodePudding

Having a couple of syntax errors in this code. I am having the "Loop is not valid at this position expecting if" at "loop", "statement is incomplete" at "END$" and "@commission is not valid at this position".

DELIMITER $$
CREATE PROCEDURE commissionCheck (IN employeeNum INT, INOUT commission VARCHAR(4000)) 
    BEGIN 
        DECLARE employee_cursor CURSOR FOR
        SELECT firstName,lastName,address,city,state,zipcode,sale
        FROM customer c, employee e, sale s, cityState cs
        WHERE s.employeeID = employeeNum;
        DECLARE CONTINUE HANDLER 
                FOR NOT FOUND SET v_finished = 1;
        OPEN employee_cursor;
        get_employee: LOOP
            FETCH employee_cursor INTO v_eFirst;
            FETCH employee_cursor INTO v_eLast;
            FETCH employee_cursor INTO v_eAddress;
            FETCH employee_cursor INTO v_eCity;
            FETCH employee_cursor INTO v_eState;
            FETCH employee_cursor INTO v_eZip;
            FETCH employee_cursor INTO v_retail;
         IF v_finished = 1 THEN 
            LEAVE get_employee;
        END IF;
        SET v_count = vcount   1;
        SELECT v_eFirst;
        SELECT v_eLast;
        SELECT v_eAddress; 
        SELECT v_eCity; 
        SELECT v_eZip;
        SELECT v_retail;
        IF v_count = 1 THEN
            IF v_retail >= 40000 THEN SET v_pay = (v_retail * 0.20)   v_retail;
            ELSEIF v_retail >= 30000 THEN SET v_pay = (v_retail * 0.15)   v_retail;
            ELSEIF v_retail >= 20000 THEN SET v_pay = (v_retail * 0.10)   v_retail;
            ELSE SET  v_pay = (v_retail * 0.08)   v_retail;
            
            SET commission = CONCAT(commission, 'FROM:\n');
            SET commission = CONCAT(commission, '\nCGS 2545 Car Dealership\n');
            SET commission = CONCAT(commission, '\UCF\n');
            SET commission = CONCAT(commission, '\MSB 260\n\n');
            SET commission = CONCAT(commission, 'Pay to the order of:\n\n');
            SET commission = CONCAT(commission, v_efirst, '', v_eLast,'\n');
            SET commission = CONCAT(commission, v_eAddress, '\n');
            SET commission = CONCAT(commission, v_eCity, ',', v_eState,'',v_eZip,'\n');
            SET commission = CONCAT(commission, 'In the amount of:\n\n');
            SET commission = CONCAT(commission, '$', v_pay, '\n');
            SET commission = CONCAT(commission, '\n', v_lines, '\n');
        END IF;
        END LOOP get_employee;
        close employee_cursor;
END$$
DELIMITER ;

SET @commission = "";
CALL @commission:{
    employeeId from sale
    @commission
}
SELECT @commission;

I havent really tried much to fix it to be honest. I am lost

CodePudding user response：

Having a couple of syntax errors in this code. ... I haven't really tried much to fix it to be honest.

There's no need to be lost. Just simplify your problem.

Currently you have a bunch of code, and a complex problem, one that is too complex to understand how to fix it. Ok.

Simplify it. Define a one-line function, see it work, then a two-line function, and build up to something that is roughly as long as what you posted.

Pay attention to the small change you introduced which changes from green to red, from working to not working. Understand what that change does, perhaps by consulting the documentation.

As it stands, you have a big blob of code that neither you nor your readers understand. Work on small problems, as they are easy to solve.

https://ericlippert.com/2014/03/05/how-to-debug-small-programs

CodePudding user response：

you are missing an end if- I don't know where it should go so I guessed see #added...
local variables need to be declared How to declare a variable in MySQL?
Having many fetchs looks wrong https://dev.mysql.com/doc/refman/8.0/en/fetch.html.

This compiles but may not be logically what you want

DROP PROCEDURE IF EXISTS P;
DELIMITER $$
CREATE PROCEDURE P (IN employeeNum INT, INOUT commission VARCHAR(4000)) 
    BEGIN 
        DECLARE v_eFirst VARCHAR(10);
        DECLARE v_eLast VARCHAR(10);
        DECLARE v_eAddress VARCHAR(10);
        DECLARE v_eCity VARCHAR(10);
        DECLARE v_eState VARCHAR(10);
        DECLARE v_eZip VARCHAR(10);
        DECLARE v_retail VARCHAR(10);
        DECLARE V_FINISHED INT;
        DECLARE v_count int;
        declare v_pay int;
        declare v_lines int;
    
        DECLARE employee_cursor CURSOR FOR
        SELECT firstName,lastName,address,city,state,zipcode,sale
        FROM customer c, employee e, sale s, cityState cs
        WHERE s.employeeID = employeeNum;
        DECLARE CONTINUE HANDLER 
                FOR NOT FOUND SET v_finished = 1;
 
        OPEN employee_cursor;
        get_employee: LOOP
            FETCH employee_cursor INTO v_eFirst,
             v_eLast,
             v_eAddress,
             v_eCity,
             v_eState,
             v_eZip,
             v_retail;
             
        IF v_finished = 1 THEN 
            LEAVE get_employee;
        END IF;
        SET v_count = vcount   1;
        SELECT v_eFirst,v_eLast,v_eAddress,v_eCity,v_eZip, v_retail;
        
        IF v_count = 1 THEN
            IF v_retail >= 40000 THEN SET v_pay = (v_retail * 0.20)   v_retail;
            ELSEIF v_retail >= 30000 THEN SET v_pay = (v_retail * 0.15)   v_retail;
            ELSEIF v_retail >= 20000 THEN SET v_pay = (v_retail * 0.10)   v_retail;
            ELSE SET  v_pay = (v_retail * 0.08)   v_retail;
            end if; #added
            
            SET commission = CONCAT(commission, 'FROM:\n');
            SET commission = CONCAT(commission, '\nCGS 2545 Car Dealership\n');
            SET commission = CONCAT(commission, '\UCF\n');
            SET commission = CONCAT(commission, '\MSB 260\n\n');
            SET commission = CONCAT(commission, 'Pay to the order of:\n\n');
            SET commission = CONCAT(commission, v_efirst, '', v_eLast,'\n');
            SET commission = CONCAT(commission, v_eAddress, '\n');
            SET commission = CONCAT(commission, v_eCity, ',', v_eState,'',v_eZip,'\n');
            SET commission = CONCAT(commission, 'In the amount of:\n\n');
            SET commission = CONCAT(commission, '$', v_pay, '\n');
            SET commission = CONCAT(commission, '\n', v_lines, '\n');
        END IF;
  
        END LOOP;
        close employee_cursor;
END $$
DELIMITER ;