I have a stored proc I need to call from yet another stored proc, which
then needs to use the results from the called proc ... I could probably
use temp table but really dont want to do that unless absolutely
required. I cant use OUT parameters, because the called stored proc
returns multiple rows.. can this be done (see the two samples below) or
is a temp table the only way?
This generates a result set
DELIMITER $$
DROP PROCEDURE IF EXISTS `getCMDBdata`$$
CREATE PROCEDURE `getCMDBdata`(IN ipadd VARCHAR(16))
BEGIN
DECLARE iid text;
select ifnull(group_concat(distinct `ia`.`ITEMID`),'0') into iid
from `federated_itemattributes` `ia` left join
`federated_attributesvarchar2` `v` on(`ia`.`ITEMATTRIBUTEID` =
`v`.`ITEMATTRIBUTEID`)
where v.value = ipadd and ia.typeattributeid = 8259;
SELECT i.itemid, i.typeattributeid, i.itemattributeid, `VALUE`
, y.typeid
, y.datatypeid
, y.description
, p.typename
FROM federated_itemattributevalues i
join itemattributes a on a.itemattributeid = i.itemattributeid
join items t on t.itemid = i.itemid
join typeattributes y on y.typeattributeid = i.typeattributeid
join types p on p.typeid = y.typeid
left outer join relationships r on r.ci1 = i.itemid and
r.relationshiptypeid IN (2667, 2684, 2704)
WHERE r.ci1 IN (iid) OR ci2 IN (iid)
ORDER BY i.itemid, y.typeid;
END$$
DELIMITER ;
This calls the above proc and will eventually want to do things with the
results:
DELIMITER $$
DROP PROCEDURE IF EXISTS `getMatch`$$
CREATE PROCEDURE `getMatch`()
BEGIN
DECLARE done0 INT DEFAULT 0;
DECLARE CID BIGINT (20);
DECLARE HIP VARCHAR (16);
DECLARE cur1 CURSOR FOR select distinct hostid, ip from hosts;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done0 = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO CID,HIP;
IF NOT done0 THEN
CALL `getCMDBdata`(HIP);
/* Do something with result above*/
END IF;
UNTIL done0
END REPEAT;
CLOSE cur1;
END$$
DELIMITER ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
