Hi All ,
I want to read Result set values of this Procedure in Mysqlserver Database .
CALL GetRecord('chive5',@R_TotalSales ,@R_MonthlySale);
| Total sale |
Total |
| 21132.6000 |
386.6000 |
The above procedure Return like this , I want read and update this value in another table. I have get TotalSale as well as Total. Can Anyone suggest with example stored Procedure.
my Procedure :
- DELIMITER $$
- CREATE DEFINER=`root`@`%` PROCEDURE `chivehq`.`GetRecord`(
- IN R_BranchName varchar(100), OUT R_TotalSales decimal(15,2),OUT R_MonthlySale decimal(15,2)
- )
- BEGIN
- DECLARE SQLStmt TEXT;
- declare R_Year date;
- declare R_Month date;
- declare R_CurrDate date;
- SET @Branch=R_BranchName;
- set @R_Year=Year(Curdate());
- set @R_Month=Month(Curdate());
- set @R_CurrDate=Curdate();
- SET @R_TotalSales=0;
- SET @R_MonthlySale=0;
- SET @SQLStmt=CONCAT
- (
- 'select sum(H.TotalSales) As TotalSales ,H.Month , sum(H.total) as Total from
- (
- select (sum(tp.PaymentAmount)) as Totalsales,Monthname(tp.BusinessDate) as Month ,0 as Total from ',R_BranchName,'.transpayment tp left join ',R_BranchName,'.trans t on tp.TransID=t.TransID where t.TransStatus in (2,3,4) and year(tp.BusinessDate)=',@R_Year,' and MONTH(tp.BusinessDate)=',@R_Month,'
- union All
- select 0.00 as Totalsales, null as Month ,sum(tp.PaymentAmount) as total from ',R_BranchName,'.transpayment tp left join ',R_BranchName,'.trans t on tp.TransID=t.TransID where tp.BusinessDate between ','@R_CurrDate',' and ','@R_CurrDate',' and t.TransStatus in (2,3,4)
- ) as H
- ');
- PREPARE Stmt FROM @SQLStmt;
- EXECUTE Stmt;
- DEALLOCATE PREPARE Stmt;
- END $$
- DELIMITER ;
Thanks in Advance ,
Karthik K