0

I have a stored Procedure that I want to use for validations in the tables, and I have a regexp that I need to compare one of the parameters, how can I compare the regexp with my parameter. I tried this:

SELECT id REGEXP '^\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}$'

id is the parameter that I receive, this is all the stored procedure:

DELIMITER &&  
CREATE PROCEDURE ValidationSalaryChange(
    IN changeDate DATE,
    IN id VARCHAR(11)
))  
BEGIN  
    IF NOT (SELECT id REGEXP '^\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}$') THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'error';
    ELSE IF (changeDate > NOW()) THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'error'; 
    END IF;
END &&  
DELIMITER ;
Diego Gamboa
  • 43
  • 7

1 Answers1

1

You don't need to use the subquery.

You have an extra ) after the parameter list.

ELSE IF needs to be one word ELSEIF.

DELIMITER &&  
CREATE PROCEDURE ValidationSalaryChange(
    IN changeDate DATE,
    IN id VARCHAR(11)
)
BEGIN  
    IF id NOT REGEXP '^\\d{1,3}\\.\\d{1,3}\\.\\d{1,3}$' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'error in id';
    ELSEIF changeDate > NOW() THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'error in changeDate'; 
    END IF;
END &&  
DELIMITER ;
Barmar
  • 741,623
  • 53
  • 500
  • 612