DROP PROCEDURE IF EXISTS sp_get_prospect_stats_for_city$
create procedure sp_get_prospect_stats_for_city
(
IN in_city_id INT,
IN in_campaign_id INT
)
BEGIN
DECLARE total_p, notcalled INT;
SET @called = 0;
SET @notcalled = 0;
SELECT count(r.residence_id)
FROM residence r, location l
WHERE r.location_id = l.location_id
AND l.location_city_id = in_city_id
INTO total_p;
SELECT count(r.residence_id)
FROM residence r, location l
WHERE r.location_id = l.location_id
AND l.location_city_id = in_city_id
AND r.residence_id NOT IN
(
SELECT cc.contacted_residence_id
FROM campaign_contact cc
WHERE cc.campaign_id=in_campaign_id
)
INTO notcalled;
SET @called = total_p - notcalled;
SET @notcalled = notcalled;
END$