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?

JDevenyi · Accepted Answer · 2021-12-15 22:49:25Z

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 ;

answered Dec 15, 2021 at 22:49

JDevenyi

1097 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

nbk · Accepted Answer · 2021-12-15 23:20:28Z

0

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

answered Dec 15, 2021 at 23:20

nbk

49.6k8 gold badges32 silver badges53 bronze badges

Collectives™ on Stack Overflow

Running a select statement within an SQL custom function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related