Greetings. I have 1 procedure that summarises data in a table for one particular location. It accepts the location ID, and returns the summary as a single record. It works well.
Now I'm trying to write a procedure that selects all location IDs, and calls this 1st procedure for every single location, outputting a summary record per location. Here is this procedure: --- CREATE [EMAIL PROTECTED] PROCEDURE `sp_GasPostingSummary`() BEGIN declare done int default 0; declare LocID int; declare my_locations cursor for select LocID from GasLocations; declare continue handler for not found set done = 1; open my_locations; repeat fetch my_locations into LocID; if not done then call sp_GasProjectedAnnualConsumption( LocID ); end if; until done end repeat; close my_locations; END --- When I call it, instead of getting the results, I get lots of empty sets: Empty set (0.04 sec) Empty set (0.04 sec) Empty set (0.04 sec) How do I get this above procedure to grab results from the 1st one ( below ), and output them to the client? Here's the 1st procedure: --- CREATE [EMAIL PROTECTED] PROCEDURE `sp_GasProjectedAnnualConsumption`( in GasLocationID int ) BEGIN /* Declare variables */ declare MaxBillDate date; declare TotalConsumption double; declare TotalSpend double; declare TotalDays int; /* Get the last bill date */ select max(BillDate) from GasPosting GP inner join GasAccountNo GA on GP.AcctNoID = GA.AcctNoID where GA.LocID = GasLocationID into MaxBillDate; /* Get consumption data for a year prior to this date */ select sum( TotalUnits ) / case when UnitType = 1 then 1000 /* Megajoules */ when UnitType = 2 then 1 /* Gigajoules */ when UnitType = 3 then 0.0036 /* kWh */ else 1 /* ? */ end as SumTotalConsumption, sum( TotalBill ) as SumTotalBill, sum( case when Days > 0 then Days else 28 end ) as SumTotalDays from GasPosting GP inner join GasAccountNo GA on GP.AcctNoID = GA.AcctNoID where LocID = GasLocationID and BillDate between date_sub( MaxBillDate, interval 1 year ) and MaxBillDate into TotalConsumption, TotalSpend, TotalDays; /* Stretch to 365 days */ select concat_ws( ', ', Address, Suburb ) as FullAddress, MIRN, round( TotalConsumption * 365 / TotalDays ) as ProjectedAnnualConsumption, round( TotalSpend * 365 / TotalDays ) as ProjectedAnnualSpend from GasLocations where LocID = GasLocationID; END --- -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]