MySQL Error When Creating Function

Question

I am getting this error when I want to execute below query for creating function, I checked everything it seems that I can't find the issue:

MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ELSEIF price > 1000 THEN BEGIN SET last3 = SUBSTRING(price, -3); ' at line 18

DELIMITER $$


CREATE FUNCTION GetPrice(number decimal(10,2), profit decimal (10,2)) RETURNS INT
    DETERMINISTIC
BEGIN
    DECLARE price int;
    DECLARE last3 int;
    DECLARE rootn int;
    SET price = CEIL(number + (number * profit /100));
    IF price < 1000 THEN
    BEGIN
      IF (price > 0 and price <= 500) THEN
      SET price = 500;
      ELSEIF (price >500 and price <= 750) THEN
      SET price = 750;
      ELSEIF (price >750 and price < 1000) THEN
      SET price = 1000;
      END IF;
    END
    ELSEIF price > 1000 THEN
    BEGIN
      SET last3 = SUBSTRING(price, -3);
      SET rootn = price-last3;
      IF (last3 >= 0 and price <= 100) THEN
      SET last3 = 0;
      ELSEIF (last3 > 100 and price <= 350) THEN
      SET last3 = 250;
      ELSEIF (last3 > 350 and price <= 600) THEN
      SET last3 = 500;
      ELSEIF (last3 > 600 and last3 <= 850) THEN
      SET last3 = 750;
      ELSEIF (last3 > 850 and price <= 1000) THEN
      SET last3 = 1000;
      END IF;
      SET price = rootn + last3;
    END
    ELSEIF price = 10000 THEN
    SET price = 1000;
    END IF;

 RETURN (price);
END$$
DELIMITER;

Can you please help me?

Code blocks are delimited with BEGIN and END, not parentheses. dev.mysql.com/doc/refman/5.7/en/begin-end.html — Bill Karwin
– Bill Karwin, Commented Mar 30, 2016 at 0:53
Thanks @BillKarwin, I got another error I will update the code :) — Akam
– Akam, Commented Mar 30, 2016 at 0:59

robsn · Accepted Answer · 2016-03-30 02:26:27Z

2

You have to finish END with a semicolon:

DELIMITER //
CREATE FUNCTION `GetPrice`(number decimal(10,2), profit decimal (10,2)) RETURNS int(11)
    DETERMINISTIC
BEGIN
    DECLARE price int;
    DECLARE last3 int;
    DECLARE rootn int;
    SET price = CEIL(number + (number * profit /100));
    IF price < 1000 THEN
    BEGIN
      IF (price > 0 and price <= 500) THEN
      SET price = 500;
      ELSEIF (price >500 and price <= 750) THEN
      SET price = 750;
      ELSEIF (price >750 and price < 1000) THEN
      SET price = 1000;
      END IF;
    END;                              -- here
    ELSEIF price > 1000 THEN
    BEGIN
      SET last3 = SUBSTRING(price, -3);
      SET rootn = price-last3;
      IF (last3 >= 0 and price <= 100) THEN
      SET last3 = 0;
      ELSEIF (last3 > 100 and price <= 350) THEN
      SET last3 = 250;
      ELSEIF (last3 > 350 and price <= 600) THEN
      SET last3 = 500;
      ELSEIF (last3 > 600 and last3 <= 850) THEN
      SET last3 = 750;
      ELSEIF (last3 > 850 and price <= 1000) THEN
      SET last3 = 1000;
      END IF;
      SET price = rootn + last3;
    END;                                   -- and here
    ELSEIF price = 10000 THEN
    SET price = 1000;
    END IF;

 RETURN (price);
END//
DELIMITER ;

SQL Fiddle

edited Mar 30, 2016 at 2:26

answered Mar 30, 2016 at 1:04

robsn

7445 silver badges21 bronze badges

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

4 Comments

Akam Over a year ago

I did that but still get an error please run the query if you have mysql installed

robsn Over a year ago

@Akam Interesting. It runs on my machine. Although sqlfiddle refuses it. But I don't know their syntax for delimiters. Please try my codeblock again, I'm running MariaDB 10.

Michael - sqlbot Over a year ago

@robsn sqlfiddle has a drop-down list where you have to select a delimiter; the sanest value available in the drop-down is // and the DELIMITER directive isn't recognized there, at all. (fwiw)

robsn Over a year ago

@Michael-sqlbot Thanks for the hint!

Collectives™ on Stack Overflow

MySQL Error When Creating Function

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