CODEWITHSUNDEEP
mysqlsqldatabasestored-proceduresstored-functions
trampi
trampi

Reputation: 2284

Can't create stored function - wrong syntax?

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.

Upvotes: 1

Views: 13583

Answers (2)

EkcenierK
EkcenierK

Reputation: 1439

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 ;

Upvotes: 7

Ashwin A
Ashwin A

Reputation: 3857

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. :-)

Upvotes: 2

Related Questions