0

I have a hierarchical relational database with taxonomies of different organisms. I have a total of 7 tables where each has a FK pointing to the table "right above". I need to create a function that recieves the "Common Name" of the organism and returns the Kindgom to whom it belongs. Below is my code on MySQL.

CREATE FUNCTION get_Kingdom(@Temp AS VARCHAR(1000)) RETURNS VARCHAR(1000) 
    AS
    BEGIN
    DECLARE @ReturnKingdom AS VARCHAR(1000)

    SET @ReturnKingdom = (SELECT kingdom.description as descriptionKingdom
                        FROM Kingdom
                        JOIN Phylum
                        ON Kingdom.idKingdom = phylum.Kingdom_idKingdom
                        JOIN Class
                        ON Phylum.idPhylum = Class.Phylum_idPhylum
                        JOIN Order
                        ON Class.idClass = Order.Class_idClass
                        JOIN Family
                        ON Order.idOrder = Family.Order_idOrder
                        JOIN Genus
                        ON Family.idFamily = Genus.Family_idFamily
                        JOIN Species
                        ON Genus.idGenus = Species.Genus_idGenus

                        WHERE Species.CommonName = @Temp)

    RETURN @ReturnKingdom;
END;

But when I try to call the function

select get_Kingdom('Dog');

I'm getting an error:

'fnc_get_reino' is not a recognized built-in function name.

Any ideas why this is happening?

Reinderien
  • 11,755
  • 5
  • 49
  • 77
Quant_Penguin
  • 1
  • The published code is not valid mysql you cannot declare user defined (@) variables. Drop the @ and code should compile. – P.Salmon May 08 '22 at 06:44
  • Does this answer your question? [How to declare a variable in MySQL?](https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql) – P.Salmon May 08 '22 at 06:48
  • After working on it, seems like the @ wasnt the problem, but rather the Call line. `select [dbo].[get_Kingdom]('Dog');` This one seems to do the job just fine. Is it still incorrect syntaxis to have the "@" there? If I remove it I get a series of syntax errors. – Quant_Penguin May 08 '22 at 10:12
  • dbo? I don't see that in the published code, together with the @ variables suggests you are working on sql-server/mssql rather than mysql , the syntax is quite different. – P.Salmon May 08 '22 at 11:05

0 Answers0