dynamic sql using INTO

--_000_0AC31FD51798B348BFB7EFD2BDEFE96E155455877CEXVMBX02012 ex_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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 i=
n 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 'd=
uration' does not work in here because it is not a proper place for a varia=
ble 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 =3D iid
AND clock > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 31 DAY)); /* this 3=
1 should be duration from the in list*/
SET @calc =3D 100 / (100 - percentile);
SET @yarp =3D cnt / @calc;
SELECT
value
INTO
temp
FROM
history
WHERE
itemid =3D 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 ;


--_000_0AC31FD51798B348BFB7EFD2BDEFE96E155455877CEXVMBX02012 ex_--