I have a function built that will get me X percentile from my history table for last X days. It works fine as long as I hard code the certain values in where they go... what I really need to know is how can I make dynamic sql still do a select into a variable? simply replacing the '31' below with 'duration' does not work in here because it is not a proper place for a variable substitution....
DELIMITER $$ -- -- Definition for function firescope.getPercentile -- DROP FUNCTION IF EXISTS firescope.getPercentile$$ CREATE FUNCTION getPercentile(iid BIGINT(20), duration INT, percentile INT) RETURNS TEXT CHARSET LATIN1 BEGIN DECLARE cnt, pct BIGINT(20); DECLARE temp TEXT; SELECT COUNT(*), AVG(value) INTO cnt FROM history WHERE itemid = iid AND clock > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 31 DAY)); /* this 31 should be duration from the in list*/ SET @calc = 100 / (100 - percentile); SET @yarp = cnt / @calc; SELECT value INTO temp FROM history WHERE itemid = iid AND clock > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 31 DAY)) /* this 31 should be duration from the in list*/ ORDER BY value DESC LIMIT 0, 1; /*this 0 should be @yarp*/ RETURN temp; END $$ DELIMITER ;