I need to create a cursor using dynamic sql. Below is a short example of
what I am trying to use in order to get the correct resultset... ignore
the rest of the proc, I just need to know how to make the cursor
declaration from @vsql
Thanks
DELIMITER $$
DROP PROCEDURE IF EXISTS `firescope`.`triggerDownTime` $$
CREATE PROCEDURE `firescope`.`triggerDownTime` (IN TrigID BIGINT(20)
unsigned,IN tRange varchar(10), OUT DownTime BIGINT(20) unsigned)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE eventTime, startTime, endTime, defaultStart, defaultEnd,
outage BIGINT(20) unsigned;
DECLARE val, alreadyStarted int(10);
set @vsql = 'select unix_timestamp(DATE_SUB(now(),INTERVAL
',tRange,')), unix_timestamp(now()), value, unix_timestamp(time_stamp)
from fs_sl_events where triggerid = 'TrigID,' AND time_stamp >
DATE_SUB(now(),INTERVAL 'tRange,')');
DECLARE cur1 CURSOR FOR vsql;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO defaultStart, defaultEnd, val, eventTime;
IF NOT done THEN
set DownTime = unix_timestamp(DATE_SUB(now(),INTERVAL 30
DAY));
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
select DownTime;
END $$
DELIMITER ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
