Problem with processing intermediate results in MySQL stored procedure

Question

Dear all,

i try to do a little weighting of my data by using a stored procedure. Basically this means multiplying certain columns with their respective weights and adding them up in the end. I have written the following stored procedure:

CREATE PROCEDURE test ()  
BEGIN
DECLARE w1 DOUBLE;
DECLARE w2 DOUBLE;
DECLARE res1 DOUBLE;
DECLARE res2 DOUBLE;
DECLARE finres DOUBLE;
SELECT weight1 INTO w1 FROM weights;
SELECT weight2 INTO w2 FROM weights;
SELECT w1 * var1 INTO res1 FROM vartable;
SELECT w2 * var2 INTO res1 FROM vartable;
SELECT res1+res2 INTO finres;
SELECT MEAN(finres);
END
//

Unfortunately it does not do the trick yet. In the end it should return one value, but all of this only works if i put all the vars to the SELECT query. Thx in advance for any suggestions!

Answer 1

According to mysql docu you have to declare the parameter you like to output

Like

CREATE PROCEDURE simpleproc (OUT param1 INT)

store the value in param1 and call the procedure with:

CALL simpleproc(@a);

and get the value with:

SELECT @a;

That should do the trick

Answer 2

Sorry, messed up with my first answer, didn't understand the problem ....

You want to calculate the MEAN over your weights multiplied with the variables. But in your procedure you only store doubles. So your MEAN calculation goes over one double. IMHO that makes no sense. E.g. The MEAN of 5 ? :-)

What is written in your tables? Only one row of weights and one row of variables?

Answer 3

Try it like this:

SELECT AVG(W.weight1 * V.var1 + W.weight2 * V.var2) FROM weights W, vars V;

Answer 4

I got it ! The following does work:

CREATE PROCEDURE test2 (IN period1 INT)
BEGIN
DECLARE x1 DOUBLE;
DECLARE x2 DOUBLE;
DECLARE param1 DOUBLE; 
SELECT wvalue INTO x1 FROM weights WHERE uid=1;
SELECT wvalue INTO x2 FROM weights WHERE uid=2;

SELECT x1*x2 INTO param1;
INSERT result_table(result,period) SELECT param1,period1;

END
//
DELIMITER ;

call test2 (@la);

Can I store this result somehow? EDIT: figured that one out, too. Store it in some existing table, see edit.