CODEWITHSUNDEEP
mysqlstored-proceduressyntax
suraj
suraj

Reputation: 13

Mysql stored function help needed

I'm just beginning to learn stored functions in mysql. Can someone please tell me whats wrong with below?

Phpmyadmin says 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 'return NAME; END //' at line 19 

DELIMITER //
DROP FUNCTION IF EXISTS getName //
CREATE FUNCTION getName(type CHAR(10), id int) RETURNS CHAR(50) DETERMINISTIC
BEGIN
    DECLARE NAME CHAR(50);
    CASE type
        WHEN 'offer' THEN 

        SELECT Type_Name INTO NAME FROM otypes WHERE Type_Id = id;          

        WHEN 'service' THEN 

        SELECT ServiceType_Name INTO NAME FROM stypes WHERE ServiceType_Id = id;    

        WHEN 'store' THEN 

        SELECT Store_Name INTO NAME FROM stores WHERE Store_Id = id;    

    END CASE

    return NAME;
END //

Upvotes: 1

Views: 67

Answers (2)

Johan
Johan

Reputation: 76537

You missing a ;

END CASE

return NAME;

should be

END CASE;

return NAME;

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425311

Put a semicolon after END CASE:

DELIMITER //
DROP FUNCTION IF EXISTS getName //
CREATE FUNCTION getName(type CHAR(10), id int) RETURNS CHAR(50) DETERMINISTIC
BEGIN
    DECLARE NAME CHAR(50);
    CASE type
        WHEN 'offer' THEN 

        SELECT Type_Name INTO NAME FROM otypes WHERE Type_Id = id;          

        WHEN 'service' THEN 

        SELECT ServiceType_Name INTO NAME FROM stypes WHERE ServiceType_Id = id;    

        WHEN 'store' THEN 

        SELECT Store_Name INTO NAME FROM stores WHERE Store_Id = id;    

    END CASE;
--          ^

    return NAME;
END //

Upvotes: 1

Related Questions