Creating MySQL Function

Question

I am trying to write a function that looks for a value assigned to its configuration in a parent-child tree, if the value is null or empty it looks one level up for the value. I am currently getting syntax errors when trying to create the function. This is what i have so far,

DELIMITER //
CREATE FUNCTION `db`.`Configuration`(
    `ColumnName` VARCHAR(128),
    `CID` INT
)
RETURNS VARCHAR(256)
NOT DETERMINISTIC
BEGIN
    DECLARE Config VARCHAR(256) DEFAULT NULL;
    DECLARE Parent INT;

    WHILE (@Config IS NULL OR @Config = "") DO
        SELECT @ColumnName INTO @Config, `ParentID` INTO @Parent FROM `Table` WHERE `ID`=@CID;
    END WHILE;

    RETURN CONCAT(@Config, '::', @Parent);
    END ;
//
DELIMITER ;

I am getting the following error when I try to add the function:

1327 - Undeclared variable: ParentID

Any help would be greatly appreciated!

Always include errors you receive in your post. They're very meaningful so make sure you paste them verbatim. — erik258, Jan 03 '18 at 22:35
You need insert into.. select as per https://dev.mysql.com/doc/refman/5.7/en/insert-select.html — user8834780, Jan 03 '18 at 22:41

score 1 · Accepted Answer · answered Jan 03 '18 at 23:23

You receive the error message in the question because you have multiple into clauses, whereas according to mysql manual on select ... into ... you can only have one. So, to get rid of this specific error message you nee to rewrite your select statement as:

SELECT @ColumnName, `ParentID` INTO @Config, @Parent FROM `Table` WHERE `ID`=@CID;

However, there are some further issues with your code:

varname and @varname do not refer to the same variable. The first one is either a function / stored proc parameter or local variable, while the 2nd one is a user-defined variable. In your code you must remove the @ from the variable names.
You cannot use a variable in place of a field name in an sql statement. You must use dynamic sql with prepared statements to achieve this. See the following SO question on how to this: How To have Dynamic SQL in MySQL Stored Procedure
You do not overwrite CID parameter in your while loop. This means that if the first iteration the configuration will remain null, then you have an infinite loop. You should change the value of CID in your loop.

I cannot guarantee that there are no further errors in your code.

score 0 · Answer 2 · answered Jan 03 '18 at 23:19

There are a few problems with your function:

You are using SELECT...INTO incorrectly. When selecting multiple values you should only use INTO once. For example SELECT a,b into @a,@b FROM...
You are using user-defined variables with similar names to your function parameters, but they are not the same thing. In your code CID and @CID are different. I suggest using standard naming prefixes to clarify this: for example use p_ for function parameters and v_ for local function variables. You shouldn't need to use user-defined variables at all.
Your WHILE loop is bound to lead to infinite loops since the query criteria never changes. If it returns NULL or empty string once, it will keep returning them forever.

Here's a quick rewrite to address the above issues. I'll leave it to you to implement the WHILE loop correctly:

DELIMITER //
CREATE FUNCTION `db`.`Configuration`(
    p_column_name VARCHAR(128),
    p_id INT
)
RETURNS VARCHAR(256)
READS SQL DATA
BEGIN
    DECLARE v_config VARCHAR(256) DEFAULT NULL;
    DECLARE v_parent INT;

    SELECT p_column_name,`ParentID` 
    INTO v_config, v_parent 
    FROM `Table` 
    WHERE `ID`=p_id;

    RETURN CONCAT(v_config, '::', v_parent);
    END ;
//
DELIMITER ;

Creating MySQL Function

1327 - Undeclared variable: ParentID

2 Answers2