Problem with creating a stored procedure

Jaco posted 1 month ago in HeidiSQL portable

Good day

I am having issues with generating a stored procedure:

DELIMITER //
CREATE PROCEDURE insertProzessRechte (prozessen_id INT, prozess_name VARCHAR(45), prozess_beschreibung VARCHAR(45), rechte_id INT, recht_name VARCHAR(45), recht_beschreibung VARCHAR(45), user VARCHAR(45), unternehmen_id INT)
BEGIN
    DECLARE procId INT DEFAULT 0//
    IF prozessen_id = '-1' THEN
        INSERT INTO prozessen (prozessname, beschreibung, erstellt_von, unternehmen_id) VALUES (prozess_name, prozess_beschreibung, user, unternehmen_id)//
        SET procId = SELECT LAST_INSERT_ID();
        INSERT INTO rechte (recht, beschreibung, erstellt_von, prozessen_id) VALUES (recht_name, recht_beschreibung, user, procId)//
    ELSE
        INSERT INTO rechte (recht, beschreibung, erstellt_von, prozessen_id) VALUES (recht_name, recht_beschreibung, user, prozessen_id)//
    END IF//
END //

Error:

[Window Title]
Local DB: Error

[Content]
SQL 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 '' at line 3

[OK]

[Footer]
Find some help on this error

I am doing this via the editor and not the build in GUI interface.

Any help?

Many thanks, Jaco

Misha v.3 posted 1 month ago

You should not use //-delitimer in you SP's body. Use ';' instead. // should only be used at the end on SP-creation command:

DELIMITER //
CREATE PROCEDURE ...
BEGIN
    /* SP's body, use ';' as a statements separator here */
END//
DELIMITER ;

Also, you should change this:

SET procId = SELECT LAST_INSERT_ID();

to this:

SET procId = LAST_INSERT_ID();
Jaco posted 1 month ago

Great! Have actually tried if first with the ';' delimiter. Problem was the "Select LAST...."

THANKS!

Please login to leave a reply, or register at first.