MySQL Function: Selecting and returning column

iraxef picture iraxef · Oct 24, 2012 · Viewed 9.3k times · Source

I have a table Processes, which consists of an ID (unsigned int, auto_increment) and Name (varchar, unique).

Selecting the following function with any input (whether in the table or not) always results in

ERROR 1172 (42000): Result consisted of more than one row

CREATE FUNCTION LookupOrInsertProcess(nametwo VARCHAR(255))
  RETURNS INT UNSIGNED
BEGIN
  DECLARE myid INT UNSIGNED;
  SELECT ID INTO myid FROM Processes WHERE Name=nametwo;
  RETURN myid;
END$$

However, selecting the below function always returns NULL:

CREATE FUNCTION LookupOrInsertProcess(nametwo VARCHAR(255))
  RETURNS INT UNSIGNED
BEGIN
  DECLARE myid INT UNSIGNED;
  SELECT ID INTO myid FROM Processes WHERE Name=nametwo;
  RETURN myid;
END$$

Furthermore, please note that the following does return the correct result (numbers 30 and 50 are arbitrary):

CREATE FUNCTION LookupOrInsertProcess(nametwo VARCHAR(255))
  RETURNS INT UNSIGNED
BEGIN
  DECLARE myid INT UNSIGNED;
  SELECT ID INTO myid FROM Processes WHERE Name=nametwo;
  IF myid IS NULL THEN
    RETURN 30;
  ELSE
    RETURN 50;
  END IF;
END$$

Any help is appreciated.

UPDATE: Edited to remove clash between table column and function param. I don't believe that's the issue.

UPDATE2: Please note that the following appears to work, both when the input param is or is not in the table. Why, without the coalesce(), does the function return NULL even for input params which are in the table?

CREATE FUNCTION LookupOrInsertProcess(nametwo VARCHAR(255))
  RETURNS INT UNSIGNED
BEGIN
  DECLARE myid INT UNSIGNED;
  SELECT ID INTO myid FROM Processes WHERE Name=nametwo;
  RETURN COALESCE(myid, 0);
END$$

Answer

paxdiablo picture paxdiablo · Oct 25, 2012

Column names are not case-sensitive in MySql so you may find that where Name = name means 'give me every row'.

Try changing the input parameter to your procedure (and the corresponding condition in the query) to be srchName or something else different to name.