i have created a stored procedure which have logic like this:
this is my stored procedure syntax:
DELIMITER $$
USE `sre`$$
DROP PROCEDURE IF EXISTS `sp_checkPotentialProductf`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_checkPotentialProductf`(IN sdate DATE, IN edate DATE)
BEGIN
DECLARE sumtotal INT DEFAULT 0;
DECLARE first_margin DOUBLE;
DECLARE count_rows INT DEFAULT 0;
DECLARE startnum INT DEFAULT 0;
DECLARE start_mrg DOUBLE;
DECLARE adder DOUBLE;
/* assign table temporary and select the values */
CREATE TEMPORARY TABLE IF NOT EXISTS vShowTopSellingQuantitys AS
SELECT
`mt_pesanan`.`ms_langganan_idms_langganan` AS `idms_langganan`,
`mt_pesanan`.`ms_langganan_idms_kodebarang` AS `idms_kodebarang`,
`md`.`nama` AS `nama`,
`mt_pesanan`.`quantity` AS `quantity`,
`mt_pesanan`.`harga` AS `harga`,
`mt_pesanan`.`jumlah` AS `jumlah`,
`mt_pesanan`.`discount` AS `discount`,
`mt_pesanan`.`tgl_pesan` AS `tgl_pesan`,
SUM(`mt_pesanan`.`quantity` ) AS `sum_product`
FROM `mt_pesanan` JOIN `ms_barang` `md`
WHERE ((`mt_pesanan`.`tgl_pesan` <= edate) AND (`mt_pesanan`.`tgl_pesan` >= sdate))
AND (md.`idms_kodebarang` = `mt_pesanan`.`ms_langganan_idms_kodebarang`)
GROUP BY `mt_pesanan`.`ms_langganan_idms_kodebarang`
ORDER BY SUM(`mt_pesanan`.`quantity` ) DESC;
/* end assign table temporary */
/* assign values to variables */
SELECT SUM(sum_product) INTO sumtotal FROM `vShowTopSellingQuantitys`;
SELECT COUNT(sum_product) INTO count_rows FROM `vShowTopSellingQuantitys`;
SELECT (sum_product/sumtotal)*100 INTO first_margin FROM `vShowTopSellingQuantitys` LIMIT 1;
/* truncate database tmp*/
TRUNCATE tmp_produkpotensial;
/*creating algorithm to analyze the percentage quantity/total */
SET startnum = 1;
/*show result with percentage*/
SELECT `idms_kodebarang`, `nama`, sum_product , ((sum_product/sumtotal)*100) AS 'pecentage_margin' FROM `vShowTopSellingQuantitys`;
/*result execution from temporary table to analyze potential product */
INSERT INTO tmp_produkpotensial(idms_kodebarang,nama,percentage_margin)
SELECT `idms_kodebarang`, `nama`, ((sum_product/sumtotal)*100) AS 'percentage_margin' FROM `vShowTopSellingQuantitys`;
/* update the sum total of percentage */
WHILE(count_rows >= startnum) DO
IF(startnum = 1) THEN
SET start_mrg = 0;
UPDATE tmp_produkpotensial SET tmp_produkpotensial.sum_margin = (percentage_margin + start_mrg) WHERE id_barang = startnum;
ELSE
SELECT sum_margin INTO start_mrg FROM tmp_produkpotensial WHERE id_barang = (startnum - 1);
UPDATE tmp_produkpotensial SET tmp_produkpotensial.sum_margin = (percentage_margin + start_mrg) WHERE id_barang = startnum;
END IF;
/* Assign Update to Update Sum Margin Column */
SET startnum = startnum + 1;
END WHILE;
END$$
DELIMITER ;
The stored procedure is successfully compiled, but when i call the stored procedure it always returns 0 rows.
syntax to call the stored procedure:
CALL `sp_checkPotentialProductf`('2012-12-31','2012-01-01');
My Questions:
thanks
okay, i have discovered a mistake. The method to call the stored procedure is
CALL `sp_checkPotentialProductf`(2012-12-31,2012-01-01);
without the quotes.