SQL Stored Routines

Question

I'm new to using stored routines, so I have some tables in my db and I need to do some calculations based on some values.

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetRatios`(IN `date` DATE)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN

SELECT SUM(a.a_b), a.report_date FROM  product a, product_groups b where b.id = a.group_id AND a.report_date = `date` AND b.report_date = a.report_date AND b.name = "WHATEVER" and a.cu = b.cu and b.cu = "CU"; /*result1*/

SELECT r.r_n FROM r_l r WHERE r.id = 1 /*result2*/
INSERT INTO new_table(name, r) VALUES(result1, result2)

END

Now my problem is I need to insert the results of those two queries into another table. What they are selecting is not important, it's just I have to different queries and I need their results on one row in another table. Thanks in advance.

Answer 1

try to create variables to store you result using a

SELECT SUM(a.a_b) into variable1 FROM  product a, product_groups b where b.id = a.group_id AND a.report_date = `date` AND b.report_date = a.report_date AND b.name = "WHATEVER" and a.cu = b.cu and b.cu = "CU";


SELECT  a.report_date into variable2 FROM  product a, product_groups b where b.id = a.group_id AND a.report_date = `date` AND b.report_date = a.report_date AND b.name = "WHATEVER" and a.cu = b.cu and b.cu = "CU";

SELECT r.r_n into variable3 FROM r_l r WHERE r.id = 1 /*result2*/

Don't forget to declare you variables !

And then

INSERT INTO new_table(name, r) VALUES(variable1, variable2) 

Just a comment you 1st query return 2 values report SUM(a.a_b) and a.report_date. And you 2nd query return r.r_n. So you've got 3 values return.

Do you want insert all the 3 values ? If yes you have to modify you new table to mach the right format :)