Below I have a function with a cursor. I have tested the cursor sql
manually and it is fine, I have put the variables into the sql inside
the cursor loop and it returns data too, BUT, executing this function
returns null even though I know the correct info is available. Am I
missing something obvious here?
CREATE TABLE `report_columns` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`column_index` smallint(6) NOT NULL,
`column_name` varchar(128) NOT NULL,
`column_alias` varchar(128) NOT NULL,
`visible` char(1) NOT NULL,
`relationship_type_id` bigint(20) NOT NULL,
`relationship_ci` bigint(20) NOT NULL,
`relationship_index` bigint(20) NOT NULL,
`graph_value` tinyint(4) NOT NULL,
`graph_label` tinyint(4) NOT NULL,
PRIMARY KEY (`report_id`,`report_column_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `report_filters` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`report_filter_id` bigint(20) NOT NULL default '0',
`filter_condition` varchar(128) default NULL,
`filter_value` varchar(128) default NULL,
`filter_special` varchar(128) default NULL,
`filter_operator` varchar(10) default NULL,
PRIMARY KEY (`report_id`,`report_column_id`,`report_filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP FUNCTION IF EXISTS firescope.testing;
CREATE FUNCTION firescope.`testing`(RepID BIGINT(20) unsigned) RETURNS
varchar(255)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE filterSQL,filterTMP, colName varchar(255);
DECLARE colID, rID BIGINT(20) unsigned;
DECLARE cur1 CURSOR FOR SELECT report_column_id, column_name
FROM report_columns
WHERE report_id = RepID ORDER BY report_column_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
SELECT concat(' ' ,rvf.filter_operator,'
', colName,' ', (case rvf.filter_condition when 'not_equal' then '<>'
when 'greater_than' then '>' else '=' end)
,' ' ,rvf.filter_value,'
') into filterTMP
FROM report_filters rvf
WHERE rvf.report_id = RepID
and rvf.report_column_id
= colID;
IF filterTMP is NOT null then
IF filterSQL is null
then
select
filterTMP into filterSQL;
ELSE
select
concat(filterSQL,filterTMP) into filterSQL;
END IF;
END IF;
SET filterTMP = null;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
return filterSQL;
END;
