MySQL stored function problem

Question

I'm creating a stored function like this

CREATE FUNCTION getVendorID(IN venname VARCHAR(255)) 
RETURNS INT
BEGIN
    DECLARE a INT;
    SELECT vendorid FROM vendors WHERE vendorname LIKE venname INTO a;
    RETURN a;
END$$

but I receive an error:

ERROR 1064 (42000): 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 'IN venname VARCHAR(255))
RETURNS INT
BEGIN
DECLARE a INT;
SELECT vendorid FRO' at line 1

Answer 1

MySQL functions only takes IN-parameters, and therefor they cannot be declared as IN.

DELIMITER $$
CREATE FUNCTION getVendorID(venname VARCHAR(255)) 
RETURNS INT
BEGIN
    DECLARE a INT;
    SELECT vendorid INTO a FROM vendors WHERE vendorname LIKE venname;
    RETURN a;
END$$
DELIMITER ;

Answer 2

1. no IN for functions
2. INTO after select list, before FROM
3. READS SQL DATA to avoid binary log issues

So your function definition should look like:

DELIMITER $$
DROP FUNCTION IF EXISTS getVendorID$$
CREATE FUNCTION getVendorID( venname VARCHAR(255) ) 
RETURNS INT
READS SQL DATA
BEGIN
  DECLARE a INT;
  SELECT vendorid INTO a FROM vendors WHERE vendorname LIKE venname;
  RETURN a;
END$$
DELIMITER ;

Answer 3

Why all that code? Use this:

CREATE FUNCTION getVendorID(IN venname VARCHAR(255)) 
RETURNS INT
BEGIN
    RETURN (SELECT vendorid FROM vendors WHERE vendorname LIKE venname LIMIT 1);
END$$

Also note introduction od LIMIT 1. Your code will explode if more than one vendor matches; you can't put the vendorid from multiple rows into one variable.

You may consider auto-wrapping with % as a service to your callers: WHERE vendorname LIKE CONCAT('%', venname, '%')