Running a select statement within an SQL custom function-CodePudding

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?

CodePudding user response：

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 ;

CodePudding user response：

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