MYSQL Function throws custom error message instantly after creating it

Question

Hi hope you guys can help.

I am trying to throw an error Message inside a Function if the Input (INT) isn't equal to length 8.

My Code:

DELIMITER $$
CREATE FUNCTION test(a INT)
RETURNS INT
BEGIN
    DECLARE ret INT;
    IF LENGTH(CONVERT(a, CHAR)) != 8 THEN SIGNAL SQLSTATE '4500' SET MESSAGE_TEXT = 'need to be 8!';
    END IF;
    IF a <= 20000000 THEN SET ret = 1;
    ELSEIF a > 20000000 AND a <= 30000000 THEN SET ret = 2;
    ELSE SET ret = 3;
    END IF;
    RETURN ret;
END;
$$
DELIMITER ;

Unfortunately when i create the Function it instantly throws the error message and doesn't wait for the condition. So when i am trying to run the Code it says:

Error Code: 1407. Bad SQLSTATE: '4500'

How can i solve that??

SQLSTATEs are 5-byte codes, you're trying to set up a 4-byte one. — Lukasz032
– Lukasz032, Commented Sep 5, 2023 at 19:10

Sagar Gangwal · Accepted Answer · 2017-06-25 10:51:17Z

3

DELIMITER $$
CREATE FUNCTION test(a INT)
RETURNS INT
BEGIN
    DECLARE ret INT;
    IF LENGTH(CONVERT(a, CHAR)) != 8 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'need to be 8!';   #--I had made changes at this line#
    END IF;
    IF a <= 20000000 THEN SET ret = 1;
    ELSEIF a > 20000000 AND a <= 30000000 THEN SET ret = 2;
    ELSE SET ret = 3;
    END IF;
    RETURN ret;
END;
$$
DELIMITER ;

Try above code. There is no SQLSTATE with 4500.Instead of 4500 you can try 45000 SQLSTATE

Hope this will help you.

answered Jun 25, 2017 at 10:51

Sagar Gangwal

8,1303 gold badges28 silver badges45 bronze badges

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

4 Comments

user7992920 Over a year ago

@Sagnar Gangwal First of all thanks a lot for your answer. I was able to create the function. Unfortunately the condition doest work. If the input isn't equal to 8 the Function doesn't calculate ret wich is fine but it also doesn't throw the message 'need to be 8!'. I tried calling the function using: SELECT test(223941245). Do you maybe see the next dumb mistake i do? haha

Sagar Gangwal Over a year ago

@user7992920 I had tried same,and got proper error message as shown in below image

P.Salmon Over a year ago

I think the ; after the end statement should be deleted. But otherwise it work for me too.

Lukasz032 Over a year ago

One correction: the SQLSTATE you can apply doesn't need to exist in the documentation, but needs to be well-formed (5-byte, with two alphanumeric characters and three number characters following). 45 is the class specifically for user-defined SQLSTATEs, but the following number can be anything, not necessarily 000. You can also use other classes, if you feel it's appropriate for your application; however keep in mind that they might someday get reserved for some other system use.

Collectives™ on Stack Overflow

MYSQL Function throws custom error message instantly after creating it

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related