Mysql: Call stored Procedure inside Procedure with return value

Question

Im trying to use this Procedure:

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_login`(
    IN in_Email VARCHAR(60),
    IN in_Pass VARCHAR(45)
    )
BEGIN
    DECLARE d_UserType VARCHAR(45);
    SET d_UserType := db.get_usertype( (SELECT ID FROM User WHERE Email = in_Email ) );

    IF (d_UserType = '1') THEN

        SELECT * FROM User inner join tableA on tableA.ID = User.tableA_ID;

    ELSEIF (d_UserType = '2') THEN

        SELECT * FROM User inner join tableB on tableB.ID = User.tableB_ID;

    ELSEIF (d_UserType = '3') THEN

        SELECT * FROM User inner join tableC on tableC.ID = User.tableC_ID;

    END IF;
END

But I get following error: ERROR 1305 (42000): FUNCTION db.get_usertype does not exist.

The

CALL db.get_usertype( (SELECT ID FROM User WHERE Email = '[email protected]') );

works fine when I test it alone. Anyone knows why it dosent work?

Ive tried:

SET d_UserType := CALL db.get_usertype( (SELECT ID FROM User WHERE Email = in_Email ) );
SET d_UserType := EXEC db.get_usertype( (SELECT ID FROM User WHERE Email = in_Email ) );
SET d_UserType := EXECUTE db.get_usertype( (SELECT ID FROM User WHERE Email = in_Email ) );

with no luck..

Thx for all the help!

KCD · Accepted Answer · 2011-11-09 21:31:28Z

2

Edit Completely ignore my previous comment about calling stored proc in a stored proc! A stored procedure doesn't return a result. You can however have a stored procedure insert into a temporary table from which you can select from. The following answer is still what you seek.

CALL db.get_usertype((SELECT ID FROM User WHERE Email = in_Email ));
SET d_UserType = (SELECT usertype FROM temp_get_usertype);

I believe what you want is to make get_usertype into a function instead. In some cases you can use a view instead.

SET d_UserType = db.get_usertype((SELECT ID FROM User WHERE Email = in_Email ))

e.g.

CREATE FUNCTION `get_usertype`(userID INT) RETURNS INT
BEGIN
    DECLARE $type INT;
    ...
    RETURN $type;
END

edited Nov 9, 2011 at 21:31

answered Nov 6, 2011 at 23:20

KCD

10.4k5 gold badges69 silver badges76 bronze badges

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

Collectives™ on Stack Overflow

Mysql: Call stored Procedure inside Procedure with return value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related