Can't create stored function - wrong syntax?

Question

I've got the following Problem.

I want to create a stored function which converts an entity_id into the corresponding sku

Here's the code:

CREATE FUNCTION `id2sku`(`entity_id_in` INT)
    RETURNS VARCHAR
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE returnvalue varchar(50);

    SELECT `sku` INTO returnvalue FROM catalog_product_entity WHERE entity_id = entity_id_in LIMIT 1;

    return returnvalue;
END

Now my problem is if i fire the query i get the following message: [Window Title] Error

SQL Error (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 'LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT' at line 3

The db im using is MySQL 5.0.51a

Thanks in advance for your ideas.

EkcenierK · Accepted Answer · 2011-09-08 14:25:50Z

MySQL by default uses ; as a delimiter, so when it encounters the ; at line 9:

 DECLARE returnvalue varchar(50);

MySQL thinks the command ends - it is trying to execute:

CREATE FUNCTION `id2sku`(`entity_id_in` INT)
    RETURNS VARCHAR(50)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE returnvalue varchar(50);

which isn't valid SQL.

Set a new delimiter:

 DELIMITER $$

 CREATE FUNCTION `id2sku`(`entity_id_in` INT)
    RETURNS VARCHAR(50)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE returnvalue varchar(50);

    SELECT `sku` INTO returnvalue FROM catalog_product_entity WHERE entity_id = entity_id_in LIMIT 1;

    return returnvalue;
END
$$

You can then change the delimiter back with:

DELIMITER ;

Ashwin A · Accepted Answer · 2011-09-08 09:58:49Z

2

This should work:

DELIMITER $$

CREATE FUNCTION `id2sku`(`entity_id_in` INT)
    RETURNS VARCHAR(50)
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
    DECLARE returnvalue VARCHAR(50);

    SELECT `sku` INTO returnvalue FROM catalog_product_entity WHERE entity_id = entity_id_in LIMIT 1;

    RETURN returnvalue;

    END$$

DELIMITER ;

You had not specified the varchar length. :-)

edited Sep 8, 2011 at 9:58

answered Sep 8, 2011 at 9:33

Ashwin A

3,83425 silver badges28 bronze badges

3 Comments

trampi Over a year ago

Nice, i think you are very close. I still get the following error, though. [Window Title] ..."for the right syntax to use near '' at line 9"

trampi Over a year ago

if i could i would accept both answers because both of you were right.

Bajrang Over a year ago

I have just copied and paste your code but generating the error Cannot load from mysql.proc. The table is probably corrupted. Please help me what is wrong here ? I am using mysql version 5.5.30-log.

Collectives™ on Stack Overflow

Can't create stored function - wrong syntax?

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related