Stored Procedure in mysql phpmyadmin #1064

Question

I'm trying to write a stored procedure in Mysql in phpmyadmin and I encountered an error:

DELIMITER //
DROP PROCEDURE IF EXISTS USP_SwitchTable //
CREATE PROCEDURE USP_SwitchTable(IN idTable1 INT, IN idTable2 INT)
AS BEGIN
    DECLARE idFirstBill INT;
    DECLARE idSecondBill INT;
    SELECT idFirstBill = id FROM bill WHERE id_table = idTable1 AND status = 0;
    SELECT idSecondBill = id FROM bill WHERE id_table = idTable2 AND status = 0;

    IF (idFirstBill IS NULL)
    BEGIN
    INSERT bill (id_table, bill_maker, status) VALLUES (idTable1, 1, 0);
    SELECT idFirstBill = MAX(id) FROM bill WHERE id_table = idTable1 AND status = 0;
    END


    IF (idSecondBill  IS NULL)
    BEGIN
    INSERT bill (id_table, bill_maker, status) VALLUES (idTable2, 1, 0);
    SELECT idSecondBill = MAX(id) FROM bill WHERE id_table = idTable2 AND status = 0;
    END

    SELECT id INTO IDBillInfoTable FROM bill_info WHERE id_bill = idSecondBill;

    UPDATE bill_info SET id_bill = idSecondBill WHERE id_bill = idFirstBill;

    UPDATE bill_info SET id_bill = idFirstBill WHERE id IN (SELECT * FROM IDBillInfoTable);

    DROP TABLE IDBillInfoTable;

END//
DELIMITER ;

And here's what it said :

Error #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BEGIN INSERT bill (id_table, bill_maker, status) VALLUES (idTable1, 1, 0);' at line 9

What can I do to solve the problem? I'm kinda new to this, thanks everyone

slaakso slaakso · Accepted Answer · 2019-10-30T18:08:23

MySQL IF-clause uses THEN END IF instead of BEGIN and END.

You also might want to use temporary table for the IDBillInfoTable so it works with concurrent use.

IF (idSecondBill  IS NULL) THEN
  INSERT INTO bill (id_table, bill_maker, status) 
  VALUES (idTable2, 1, 0);

  SELECT MAX(id) into idSecondBill
  FROM bill 
  WHERE id_table = idTable2 AND status = 0;
END IF;

and temporary table creation:

CREATE TEMPORARY TABLE IDBillInfoTable AS 
SELECT id
FROM bill_info 
WHERE id_bill = idSecondBill;

and dropping the temp table

DROP TEMPORARY TABLE IDBillInfoTable;

Stored Procedure in mysql phpmyadmin #1064

1 Answers