Mysql:使用返回值调用过程内部的存储过程

Question

我正在尝试使用此过程:

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

但我收到以下错误:ERROR 1305 (42000): FUNCTION db.get_usertype does not exist.

CALL db.get_usertype( (SELECT ID FROM User WHERE Email = 'example@example.com') );           

我单独测试时工作正常。任何人都知道为什么它不起作用？

我试过:

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 ) );

运气不好..

感谢所有帮助!

Answer 1

编辑 完全忽略我之前关于在存储过程中调用存储过程的评论!存储过程不返回结果。但是，您可以将存储过程插入到一个临时表中，您可以从中进行选择。下面的答案还是你要找的。

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

我相信你想要的是制作get_usertype进入 function反而。在某些情况下，您可以改用 View 。

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

例如

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