MySQL problem using CREATE FUNCTION in cPanel

Question

I have been receiving always an error while creating mysql function in cpanel with below code. But the below code is running properly while creating mysql function in SQL Manger 2007. Anyone tell me what is wrong with this code for cpanel.

ERROR :- 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 '' at line 12 (0.81 sec)

CREATE FUNCTION F_test (topsbwynum INTEGER)
  RETURNS float(20,2)
  NOT DETERMINISTIC
  CONTAINS SQL
  SQL SECURITY DEFINER

BEGIN
    DECLARE FinalPrice VARCHAR(20); 
    DECLARE err VARCHAR(20); 
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET err = 1;

    IF(sbwynum > 0)THEN
        IF(FinalPrice > 0)THEN
            RETURN FinalPrice;
        ELSEIF(adjprice > 0)THEN 
            RETURN adjprice;
        ELSEIF(price > 0)THEN
            RETURN price;
        ELSEIF(estprice > 0)THEN
            RETURN estprice; 
        ELSE 
            RETURN 0; 
        END IF;
    ELSE 
        RETURN 0; 
    END IF;
END;

Thanks a lot.

Can you give error you have please ? You can try a ; after function declaration, but I think it isn't it. — Elorfin
– Elorfin, Commented May 10, 2011 at 7:24

outis · Accepted Answer · 2011-05-10 08:55:50Z

2

For multi-line statements, you can't use the same character (";", by default) to delimit the individual statements within the function definition and the entire CREATE statement. If you try, the creation statement ends at the first ";" (in the sample code, it's after the first DECLARE). Since the statement syntax error is at the end of the truncated statement, the error message gives '' as the part of the statement where the error occurs.

From the command line client, you'd use the delimiter command to change the delimiter used by the client to (e.g.) ";;", then end the creation statement with this delimiter:

delimiter ;;
CREATE FUNCTION F_test (topsbwynum INTEGER)
    ...
END;;
delimiter ;

In phpMyAdmin, there's a "delimiter" field that fulfills the same purpose.

answered May 10, 2011 at 8:55

outis

77.7k23 gold badges155 silver badges228 bronze badges

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

Collectives™ on Stack Overflow

MySQL problem using CREATE FUNCTION in cPanel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related