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;