Running a select statement within an SQL custom function

Question

Trying to run a function in SQL with a Select statement nested inside, can't get it to work. New to this so I could use the help. Here's my code:

 DELIMITER//
 CREATE FUNCION whereAndWhen(species, VARCHAR(255))
 RETURNS INT()
 BEGIN
 SELECT animals.zone
 FROM animals
 WHERE animals.species = species;
 RETURN;
 END; //
 DELIMITER ;

The goal is for the function to accept an animal species name and receive the zone where that animal lives, all from the same table. Any tips?

Answer 1

Try using a DECLARE and a SET to hold your SELECT Query before you return it:

DELIMITER//
 CREATE FUNCION whereAndWhen(species, VARCHAR(255))
 RETURNS INT()
 BEGIN
 DECLARE zoneNum INT;
 SET zoneNum = (SELECT animals.zone
 FROM animals
 WHERE animals.species = species);
 RETURN zoneNum;
 END; //
DELIMITER ;

Answer 2

You can use SELECT INTO

CREATE tABLE animals (zone int, species varchar(255))

INSERT INTO animals VALUES (1,'Owl')

 CREATE FUNCTION whereAndWhen(_species VARCHAR(255))
 RETURNS INT
 BEGIN
 SELECT animals.zone INTO @zone
 FROM animals
 WHERE animals.species = _species;
 RETURN @zone;
 END;

SELECT whereAndWhen('Owl')

| whereAndWhen('Owl') |
| ------------------: |
| 1 |

db<>fiddle here