Mysql: Calling mysql stored procedure function inside mysql view

Question

I have a stored procedure and a view separately created. I am trying to call minVal and maxVal from Stored Procedure to my view but I don't know why am I unable to call it out using MySql Workbench

Stored Procedure

CREATE DEFINER=`root`@`localhost` PROCEDURE `getMinMaxVal`(
    IN age int,
    OUT minVal double(8,2), 
    OUT maxVal double(8,2))
BEGIN
    SET minVal = 45.64;
    SET maxVal = 55.97;
END

MySql View

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `test_view` AS
    SELECT
        getMinMaxVal(`user_data`.`age`,minVal) AS `min_range`,
        getMinMaxVal(`user_data`.`age`,maxVal) AS `max_range`,
    FROM
        (`reports`
        JOIN `user_data` ON (`reports`.`uhd` = `user_data`.`id`))
    WHERE
        `reports`.`active` = 1
    ORDER BY `reports`.`created_at` DESC

What is `getWeightRanges()`, how does it related to `getMinMaxval()`? — Barmar, Aug 02 '21 at 21:13
A stored procedure can't be used in a `SELECT` query. Maybe you should write a function instead. But it has to return a value, functions can't have `OUT` parameters. — Barmar, Aug 02 '21 at 21:16
Does this answer your question? [MySQL stored procedure vs function, which would I use when?](https://stackoverflow.com/questions/3744209/mysql-stored-procedure-vs-function-which-would-i-use-when) — Tangentially Perpendicular, Aug 02 '21 at 21:56
@Barmar oops there was something else I pasted by mistake. have updated the code above — Hardik Sisodia, Aug 02 '21 at 22:05
@Barmar Do you mean that we'd have to create store procedure inside view as well? — Hardik Sisodia, Aug 02 '21 at 22:06
@TangentiallyPerpendicular no this doesn't, I'm trying to get the output value of the stored procedure inside my view after passing age parameter into it from my view — Hardik Sisodia, Aug 02 '21 at 22:08
No. I mean you can't use a stored procedure in a view. Stored procedures are called with `CALL getMinMaxVal(age, @minVal, @maxVal)` not `SELECT`. — Barmar, Aug 02 '21 at 22:09
You're also not even calling it correctly. The SP requires 3 arguments: one input argument and two output arguments. You don't call it twice to get each output. — Barmar, Aug 02 '21 at 22:09
I did try using call method but I don't know why it was throwing errors and then in some answers it was given just the way I did I wonder how come those answers were marked accepted. It didn't work for me — Hardik Sisodia, Aug 02 '21 at 22:11

score 1 · Accepted Answer · answered Aug 02 '21 at 22:13

You need to use stored functions, one for each value.

CREATE FUNCTION getMinVal(IN age INT) RETURN FLOAT
RETURN 45.64;

CREATE FUNCTION getMaxVal(IN age INT) RETURN FLOAT
RETURN 55.97;

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW `test_view` AS
    SELECT
        getMinVal(`user_data`.`age`) AS `min_range`,
        getMaxVal(`user_data`.`age`) AS `max_range`,
    FROM
        (`reports`
        JOIN `user_data` ON (`reports`.`uhd` = `user_data`.`id`))
    WHERE
        `reports`.`active` = 1
    ORDER BY `reports`.`created_at` DESC

Thanks a lot this worked for me. Do vote up the question if you think this would help other, because I saw many others finding similar thing. I hope this one would help them out — Hardik Sisodia, Aug 02 '21 at 22:26

Mysql: Calling mysql stored procedure function inside mysql view

1 Answers1