Unknown column error in mysql stored function

Question

Following is my code of MySQL Stored Function.

DELIMITER $$

USE `mac_db`$$

DROP FUNCTION IF EXISTS `moving_average`$$

CREATE DEFINER=`root`@`localhost` FUNCTION `moving_average`(`table_name` VARCHAR(255),`column_name` VARCHAR(255),`order_column` VARCHAR(255),`row_cnt` INT) RETURNS DOUBLE

    DETERMINISTIC

BEGIN   DECLARE result_avg DOUBLE DEFAULT 0;

    SELECT AVG(`column_name`) INTO result_avg FROM table_name ORDER BY order_column DESC LIMIT row_cnt;

    RETURN result_avg;

    END$$

DELIMITER ;

I am calling the function as follows:

SELECT moving_average('my_table','my_col','id',4);

I am getting the following error.

Table 'mac_db.table_name' doesn't exist

Please help to resolve the error.

You cannot parameterize an identifier (such as a table or column name), so you have to use dynamic SQL -- prepare. — Gordon Linoff
– Gordon Linoff, Commented Feb 27, 2018 at 11:41

Nidhi257 · Accepted Answer · 2018-02-27 11:43:59Z

0

table_name is a parameter not the name of table.

replace you proc parameter as below:

DELIMITER $$

USE mac_db$$

DROP FUNCTION IF EXISTS moving_average$$

CREATE DEFINER=root@localhost FUNCTION  moving_average(@table_name VARCHAR(255),@column_name VARCHAR(255),@order_column VARCHAR(255),@row_cnt INT) RETURNS DOUBLE

DETERMINISTIC
BEGIN DECLARE result_avg DOUBLE DEFAULT 0;

SELECT AVG(@column_name) INTO result_avg FROM @table_name ORDER BY @order_column DESC LIMIT @row_cnt;

RETURN result_avg;

END$$
DELIMITER ;

answered Feb 27, 2018 at 11:43

Nidhi257

9973 gold badges8 silver badges26 bronze badges

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

2 Comments

Vrajesh Doshi Over a year ago

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@table_name VARCHAR(255),@column_name VARCHAR(255),@order_column VARCHAR(255),@r' at line 1

Nidhi257 Over a year ago

try removing @sign while defining variable. @table_name VARCHAR(255) with table_name VARCHAR(255)

Collectives™ on Stack Overflow

Unknown column error in mysql stored function

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related