asked on

Stored Procedure to calculate sum

hi,

i am having trouble writing a stored function in MySQL 5 that will return the sum of a series of values. i wrote this so far

CREATE FUNCTION `Max_Peso`(IN tbl CHAR(64), IN col CHAR(64), OUT s INT) RETURNS INT
READS SQL DATA
SQL SECURITY INVOKER
COMMENT 'Sums all the maximum \'pesos\' grouped by category'
BEGIN
SET @s = CONCAT('SELECT SUM(MAX(' , col , ')) FROM ' , tbl, ' GROUP BY categoria');
PREPARE stmt FROM @s;
EXECUTE stmt;

RETURN s;
END;

but this is not working i get an error.
please help

snoyes_jw

Functions do not have in/out parameters. If you intend to make this a function, drop the "IN" from the parameter list, and do not include the 's' parameter. If you intend to make this a procedure, change "FUNCTION" to "PROCEDURE" and adjust the "return" accordingly.

Dynamic SQL is not allowed in stored functions.

newyuppie

ASKER

thanks for the clarification. i now have this:

CREATE PROCEDURE `gr`(IN col VARCHAR, IN tbl VARCHAR)
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
SET @s = CONCAT('SELECT SUM(MAX(' , col , ')) FROM ' , tbl, ' GROUP BY categoria');
PREPARE stmt FROM @s;
EXECUTE stmt;
END

and still get error. could you help me construct this procedure?

snoyes_jw

varchar fields must have a length specified, such as varchar(64)

newyuppie

ASKER

ok UPDATE

i was missing in the IN col VARCHAR, missing to put (255) in the end. it now works, code is like this:
CREATE PROCEDURE `gr`(IN PCol VARCHAR(255), IN Ptbl VARCHAR(255))
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
SET @s = CONCAT('SELECT MAX(' , PCol , ') FROM ' , PTbl, ' GROUP BY categoria');
PREPARE stmt FROM @s;
EXECUTE stmt;
DROP PREPARE stmt;
END

i get a list of the MAX values for each category. how do i SUM all these, and return the single summed value?

ASKER CERTIFIED SOLUTION

snoyes_jw

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

newyuppie

ASKER

thanks

i removed the INTO s and excluded the OUT s INT from the declaration (because i kept getting errors). without these things, it works and returns a single row with the appropiate number,

thanks for your help!