Nested Stored Procedure in MYSQL has error

Question

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.

Is GetUserId a function or a stored procedure?

user330315
– user330315

2013-09-22 07:21:32 +00:00
Commented Sep 22, 2013 at 7:21 — user330315
– user330315, Commented Sep 22, 2013 at 7:21

fancyPants · Accepted Answer · 2013-09-22 07:47:58Z

3

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 ;

edited Sep 22, 2013 at 7:47

answered Sep 22, 2013 at 7:24

fancyPants

52.1k34 gold badges94 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

bezzoon Over a year ago

I dropped the old procedure, now this one is giving me issues CREATE PROCEDURE GetUserId(IN USER_EMAIL VARCHAR(40), OUT USER_ID INT) BEGIN SELECT ID INTO USER_ID FROM USERS WHERE EMAIL=USER_EMAIL; END

fancyPants Over a year ago

No, don't use the same names for variables and columns.

bezzoon Over a year ago

I am not sure what you mean, yo

fancyPants Over a year ago

The procedure is just an example, I hope you haven't used it 1:1. And you have replaced my parameter names starting with p_ with USER_, which are the column names of your heroes table. Don't do that, it'll confuse MySQL. Have you used appropriate delimiter? What's the error message?

bezzoon Over a year ago

I changed it to p_ and get the 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 '' at line 3

|

noufalcep · Accepted Answer · 2016-08-25 03:44:01Z

1

Old thread but could help some one looking for later... Base on fancyPants answer but more beautiful like this:

 DELIMITER $$
    CREATE FUNCTION GetUserId(IN p_email varchar(20)) RETURNS int
    BEGIN
        RETURN(SELECT id FROM users where email = p_email);
        /*or whatever your function does*/
    END$$
    DELIMITER ;

And then:

DELIMITER $$
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 (GetUserId(USER_EMAIL), NAME, GENDER);
END$$
DELIMITER ;

edited Aug 25, 2016 at 3:44

noufalcep

3,54415 gold badges36 silver badges54 bronze badges

answered Aug 25, 2016 at 3:03

Tuan Nguyen

213 bronze badges

Collectives™ on Stack Overflow

Nested Stored Procedure in MYSQL has error

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related