Error with mysql procedures ERROR 1304 & ERROR 1305

Question

I am new to using procedures and cannot seem to get mine working. I am using MySQL v5.1.36 and inputing my code using MySQL Console on a WAMPP server. If I go to (re)create the procedure. I get error #1304 (42000).

mysql>  DELIMITER //
mysql>
mysql>  CREATE PROCEDURE modx.getCRID (IN x VARCHAR(255),OUT y INT)
    ->  BEGIN
    ->          DECLARE y INT;
    ->          SELECT id INTO y
    ->          FROM `modx`.coverage_region
    ->          WHERE `coverage_region`.name = x;
    ->  END//
ERROR 1304 (42000): PROCEDURE getCRID already exists
mysql>
mysql>  DELIMITER ;

However, if I try to use the procedure I get error #1305 (42000).

mysql> USE modx;
Database changed
mysql> SET @crID = modx.getCRID("South East");
ERROR 1305 (42000): FUNCTION modx.getCRID does not exist

If the procedure exists for one how can it not exist for the other? What am I doing wrong.

Answer 1

I believe the problems are

1. The first error occurs because you are attempting to re-create an existing procedure. If you were to drop the procedure first you would not get this error,
2. The second error is because PROCEDUREs are invoked with CALL statements, while FUNCTIONs are invoked as function references, as in your code. You must define a FUNCTION, not a PROCEDURE. The (MySQL documentation) says:

The CREATE FUNCTION statement is also used in MySQL to support UDFs (user-defined functions). See Section 21.2, “Adding New Functions to MySQL”. A UDF can be regarded as an external stored function. Stored functions share their namespace with UDFs. See Section 8.2.3, “Function Name Parsing and Resolution”, for the rules describing how the server interprets references to different kinds of functions.

To invoke a stored procedure, use the CALL statement (see Section 12.2.1, “CALL Syntax”). To invoke a stored function, refer to it in an expression. The function returns a value during expression evaluation.