Skip navigation links

MySQL Forums :: Stored Procedures :: Using Dynamic Query in Function


Advanced Search

Using Dynamic Query in Function
Posted by: Cory Hubert ()
Date: November 17, 2012 06:50PM

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.

Options: ReplyQuote


Subject Views Written By Posted
Using Dynamic Query in Function 1042 Cory Hubert 11/17/2012 06:50PM
Re: Using Dynamic Query in Function 567 Peter Brawley 11/17/2012 08:51PM
Re: Using Dynamic Query in Function 461 Cory Hubert 11/17/2012 10:30PM
Re: Using Dynamic Query in Function 521 Rick James 11/17/2012 10:51PM
Re: Using Dynamic Query in Function 446 Cory Hubert 11/17/2012 11:05PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.