MySQL Procedural Languages

Question

SQL query: Documentation

CREATE PROCEDURE tree_add_root()
BEGIN
START TRANSACTION;
$max = SELECT MAX(`rht`) FROM trees;
INSERT INTO trees(`tree_id`, `name`, `label`, `description`, `lft`, `rht`, `lvl`) VALUES(1, 'Index', 'Index', '', 1,$max+1 , 0);
COMMENT;
END;

MySQL said: Documentation

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '= SELECT MAX(rht) FROM trees; INSERT INTO trees(tree_id, name, label, `' at line 4

how can I solve this?

Answer 1

Use This

CREATE PROCEDURE tree_add_root()
BEGIN
DECLARE v_max int(11);
START TRANSACTION;
SELECT MAX(`rht`) into v_max FROM trees;
INSERT INTO trees(`tree_id`, `name`, `label`, `description`, `lft`, `rht`, `lvl`) VALUES(1, 'Index', 'Index', '', 1,v_max+1 , 0);
COMMIT;
END;

Before using any variable in MySQL procedure, required to declare first using DECLARE keyword like above query, after start the body (BEGIN). using here v_max instead of $max.

Also COMMIT Keyword use instead of comment.

Answer 2

Following the comment I made in your question..

..you can try this one, mate:

DROP PROCEDURE IF EXISTS `tree_add_root`;
DELIMITER //
CREATE PROCEDURE `tree_add_root` ()
BEGIN
    START TRANSACTION;      
    SET @max = (SELECT MAX(`rht`) FROM trees);      
    INSERT INTO trees (`tree_id`, `name`, `label`, `description`, `lft`, `rht`, `lvl`)
    VALUES (1, 'Index', 'Index', '', 1, (@max + 1), 0);     
    COMMIT;
END//
DELIMITER ;

So that whenever you call the function, all you need to execute is:

CALL tree_add_root();

I hope this one can help you, cheers!