bezzoon bezzoon - 3 months ago 8
MySQL Question

Nested Stored Procedure in MYSQL has error

So I have this stored procedure, that I have tried to write a few different way, all to no avail.

CREATE PROCEDURE `CreateHero`
(IN USER_EMAIL VARCHAR(40), IN NAME VARCHAR(16), IN GENDER VARCHAR(6))
BEGIN
INSERT INTO HEROES (USER_ID, NAME, GENDER)
VALUES
((CALL GetUserId(USER_EMAIL)), NAME, GENDER);
END


I am getting this error


#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 'CALL GetUserId(USER_EMAIL)), NAME, GENDER)' at line 6


I have tried tinkering with for a while.

GetUserId works. I tried to store the result in a temporary variable and then insert it but that did not work.

Not to be shameless but If you can determine a solution, a solution where the CALL GetUserId is stored in a variable would be best.

Answer

You can't use it like this. Rewrite your GetUserId procedure with an OUT parameter.

Something like this:

DELIMITER $$
CREATE PROCEDURE GetUserId(IN p_email varchar(20), OUT p_id int)
BEGIN
    SELECT id INTO p_id FROM users where email = p_email;
    /*or whatever your procedure does*/
END$$
DELIMITER ;

Then your procedure CreateHero would look like this:

DELIMITER $$
CREATE PROCEDURE `CreateHero`
(IN USER_EMAIL VARCHAR(40), IN NAME VARCHAR(16), IN GENDER VARCHAR(6))
BEGIN
    DECLARE v_id int;
    CALL GetUserId(USER_EMAIL, v_id);
    INSERT INTO HEROES (USER_ID, NAME, GENDER) 
    VALUES 
    (v_id, NAME, GENDER);
END$$
DELIMITER ;