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$$
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
.