EAV Validation

gistfile1.sql

DROP PROCEDURE IF EXISTS `property_value_validate`;
DELIMITER //
CREATE PROCEDURE `property_value_validate`(IN _property_id INT, IN _value_data LONGTEXT) 
BEGIN 
    DECLARE _validationFails CONDITION FOR SQLSTATE '99001';
    DECLARE _dataType INT;
    DECLARE _dataTypeRegExp VARCHAR(255);    
    SET _dataType = (SELECT DISTINCT `property_datatype` FROM `properties` WHERE `property_id`= _property_id );
    SET _dataTypeRegExp = (SELECT DISTINCT `datatype_validate` FROM `property_datatypes` WHERE `datatype_name`= _dataType );
    IF (_dataTypeRegExp IS NOT NULL) THEN
            IF (_value_data NOT REGEXP _dataTypeRegExp) THEN
                SIGNAL _validationFails SET MESSAGE_TEXT= "The inserted value fails the property datatype validation rule";
            END IF;
    END IF;
END//
DELIMITER ;