Syntax error when creating a MySQL function

Question

I'm trying to create a function in MySQL, but getting a syntax error. This is my function:

DELIMITER $$
CREATE FUNCTION gg () 
RETURNS char(8)
DETERMINISTIC
BEGIN 
  DECLARE cod varchar(8);
  select RIGHT(MAX(idCurso),7) into cod from Curso;
  IF cod is null then
        set cod = 'C' + right('0000000' + convert(varchar(7),1),7);
    else
        set cod = 'C' + right('0000000' + convert(varchar(7), @id +1),7)
    return cod;
END$$
DELIMITER ;

I am getting this error in the set cod after the if cod is null then:

"cod" is not valid at this position, expecting an identifier

What am I doing wrong?

@Mario, can you explain what you want to achieve with this line set cod = 'C' + right('0000000' + convert(varchar(7),1),7); — Dark Knight
– Dark Knight, Commented Sep 10, 2019 at 16:16
Last I checked, MySQL does not use + for concatenation; but it's been a while since I checked. Also, it looks like the arguments for CONVERT might be backwards...and you have no END IF Make sure you are using documentation for MySQL, not MSSQL, nor Oracle, nor sqlite, etc.. — Uueerdo
– Uueerdo, Commented Sep 10, 2019 at 16:19
+ is an arithmetic operator in mysql use concat instead.You aren't terminating your second set statement and your first set statement doesn't make sense. — P.Salmon
– P.Salmon, Commented Sep 10, 2019 at 16:21
@JitendraYadav I'm trying to create an id with this structure: 'C000000X' being 'x' a consecutive number. — Mario Vasquez
– Mario Vasquez, Commented Sep 10, 2019 at 16:22

Dark Knight · Accepted Answer · 2019-09-10 16:44:09Z

3

There are many issues with your function.

+ icon doesn't perform concatenation in MySQL, it is used for sum. Use CONCAT or CONCAT_WS for the same.
Improper use of CONVERT. Please see documentation CONVERT
Use LPAD to pad 0 to the left of your integer value and make it length to 7.
Removed @a, don't know from where you're accessing it, replaced it with cod value and adding 1 to it.
Missed END IF clause.

DELIMITER $$
CREATE FUNCTION gg () RETURNS CHAR(8) DETERMINISTIC 
BEGIN 
    DECLARE cod VARCHAR(8);
    SELECT
        RIGHT(MAX(idCurso),7) INTO cod
    FROM Curso; 

    IF cod IS NULL THEN 
        SET cod = CONCAT('C', LPAD(1, 7, '0')); 
    ELSE 
        SET cod = CONCAT('C', LPAD(cod + 1, 7, '0')); 
    END IF; 
    RETURN cod; 
END$$
DELIMITER ;

edited Sep 10, 2019 at 16:44

answered Sep 10, 2019 at 16:37

Dark Knight

6,6211 gold badge22 silver badges40 bronze badges

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

1 Comment

Dark Knight Over a year ago

@MarioVasquez, happy to help. For any MySQL statement, check whether it is right and how to use it. This is the best way to learn.

Collectives™ on Stack Overflow

Syntax error when creating a MySQL function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related