Using Dynamic Query in Function

[Cory Hubert]

I know I am pretty close... I have a table with regular expressions.... I have successfully retrieved the Regex Expression... Now I am trying to concat a simple query that will test to see if the value is valid.

What am I doing wrong here? See Below...



DROP FUNCTION IF EXISTS CountryRegEx;
CREATE FUNCTION CountryRegEx(COUNTRY_CODE char(2), POSTAL_CODE varchar(255))
RETURNS BOOLEAN
READS SQL DATA
BEGIN
DECLARE REGEX_VAL VARCHAR(255);
DECLARE TO_RETURN_REGEX VARCHAR(255);
DECLARE done int default 0;
DECLARE isMATCH int;
DECLARE isVALID BOOLEAN;
DECLARE cur1 CURSOR FOR select MYSQL_REGEX_POSIX_1003_2 from POSTAL_CODE_REGEX_MANUAL where ISO_3166_ALPHA_2 =COUNTRY_CODE;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

open cur1;
repeat
FETCH cur1 INTO REGEX_VAL;

SELECT POSTAL_CODE REGEX REGEXVAL INTO isMATCH;

IF isMATCH = 1 THEN SET isVALID = TRUE;
END IF;

SET isMATCH = 0;
until done end repeat;

RETURN(isVALID);
END;



Edited 1 time(s). Last edit at 11/17/2012 06:59PM by Cory Hubert.