IF ELSE statement is producing invalid syntax in MySQL

Question

Not sure why this is returning invalid syntax:

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddColor`(
                            IN hexvalue varchar(7),
                          IN notes varchar(50))
    BEGIN
    IF hexvalue REGEXP '^#[0-9A-F]{6}$' THEN

       INSERT INTO data.colors(hexvalue,notes) VALUES (hexvalue,notes);

    ELSE

       SIGNAL SQLSTATE '400'
       SET MESSAGE_TEXT = 'Invalid hex value specified';

    END IF;
    END

There doesn't seem to be any errors when writing it in.

Are you sure you have issued DELIMITER // or similar as described here? dev.mysql.com/doc/refman/8.0/en/stored-programs-defining.html You code works otherwise except SQLSTATE '400' should be changed to a 5 digit number. — Salman Arshad
– Salman Arshad, Commented Mar 16, 2019 at 20:34
@SalmanA SQLSTATE not being 5 characters was the problem. Thanks — user10176607
– user10176607, Commented Mar 16, 2019 at 20:56

Hadi · Accepted Answer · 2019-03-16 20:48:27Z

1

Based on the following official documentation:

The condition_value in a SIGNAL statement indicates the error value to be returned. It can be an SQLSTATE value (a 5-character string literal) or a condition_name that refers to a named condition previously defined with DECLARE ... CONDITION (see Section 13.6.7.1, “DECLARE ... CONDITION Syntax”).

Try the following syntax and change SIGNAL SQLSTATE value to 04000:

CREATE DEFINER=`root`@`localhost` PROCEDURE `AddColor`(
                    IN hexvalue varchar(7),
                    IN notes varchar(50))
BEGIN
IF  (hexvalue REGEXP '^#[0-9A-F]{6}$') THEN

        INSERT INTO data.colors(`hexvalue`,`notes`) VALUES (hexvalue,notes);

    ELSE

        SIGNAL SQLSTATE '04000' SET MESSAGE_TEXT = 'Invalid hex value specified';

    END IF;
END;

edited Mar 16, 2019 at 20:48

answered Mar 16, 2019 at 20:17

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user10176607 Over a year ago

That did not work. Still says syntax error. I should add that the IF statement worked just fine as is. The error occurs after adding the ELSE statement. So the problem must be there somewhere

Hadi Over a year ago

@kravb try putting SIGNAL SQLSTATE '400' SET MESSAGE_TEXT = 'Invalid hex value specified'; on the same row

Hadi Over a year ago

I am getting error with SQLSTATE 400 Bad SQLSTATE: '400'

eckes Over a year ago

Try another sql state value then

Hadi Over a year ago

@kravb it is not throwing a syntax error, you didn't mentioed the right exception

|

Collectives™ on Stack Overflow

IF ELSE statement is producing invalid syntax in MySQL

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related