Using a custom delimiter got error in MySQL?

Question

Modifed.

DROP FUNCTION IF EXISTS PersonName;
DELIMITER |;

CREATE FUNCTION PersonName( personID SMALLINT )
RETURNS CHAR(20)
BEGIN
  DECLARE pname CHAR(20) DEFAULT '';
  SELECT name INTO pname FROM family WHERE ID=personID;
  RETURN pname;
END;
|
DELIMITER ;

whats wrong with this code? i get following error with it.

There seems to be an error in your SQL query. The MySQL server error output below, if there is any, may also help you in diagnosing the problem

ERROR: Unknown Punctuation String @ 102 STR: |; SQL: DROP FUNCTION IF EXISTS PersonName;# MySQL returned an empty result set (i.e. zero rows).

DELIMITER |; DELIMITER |; DELIMITER |; DELIMITER |; DELIMITER |; DELIMITER |; DELIMITER |;

SQL query:

DELIMITER |;

MySQL said: Documentation #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 'DELIMITER |' at line 1

Answer 1

I would remove the semicolon after END.

    ...
END
|
DELIMITER ;

---

Re your comment, you can't use the current delimiter when declaring a new delimiter. That sounds confusing, but consider if you do this:

DELIMITER |;

Now MySQL would think the delimiter is "|;" (two characters, a pipe and a semicolon). If you think about it, DELIMITER must be treated in a special way by the MySQL client. It's the only statement that can't be followed by the current delimiter.

So when setting the delimiter to pipe, do this:

DELIMITER |

When setting it back to semicolon, do this:

DELIMITER ;

---

FWIW, I ran the following with no error on my local test database on MySQL 5.0.75:

DROP FUNCTION IF EXISTS PersonName;
DELIMITER |

CREATE FUNCTION PersonName( personID SMALLINT )
RETURNS CHAR(20)
BEGIN
  DECLARE pname CHAR(20) DEFAULT '';
  SELECT name INTO pname FROM family WHERE ID=personID;
  RETURN pname;
END
|
DELIMITER ;