CODEWITHSUNDEEP
mysqlsql
user10176607
user10176607

Reputation:

IF ELSE statement is producing invalid syntax in MySQL

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.

Upvotes: 2

Views: 76

Answers (1)

Hadi
Hadi

Reputation: 37313

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;

Upvotes: 1

Related Questions