CODEWITHSUNDEEP
mysqlfunction
dzd
dzd

Reputation: 11

MySQL stored function problem

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

Upvotes: 1

Views: 3746

Answers (3)

Bohemian
Bohemian

Reputation: 425033

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, '%')

Upvotes: 1

Björn
Björn

Reputation: 29381

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 ;

Upvotes: 3

piotrm
piotrm

Reputation: 12356

  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 ;

Upvotes: 2

Related Questions