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 DEFINER=`root`@`10.146.%` 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 DEFINER=`root`@`10.146.%` 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: dkasak@nusconsulting.com.au
website: http://www.nusconsulting.com.au