Simple sql function with SELECT

Question

I try to understand how functions work. I can make the equivalent in procedure but I can't create a simple function with select.

element is UNIQUE and thing is PRIMARY

CREATE DEFINER=`root`@`localhost`
FUNCTION `get_element_by_thing`(`thing` VARCHAR(255))
RETURNS VARCHAR(255)
CHARSET utf8
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER
DECLARE @return_element VARCHAR(255);
SET @return_element = (
 SELECT
 `element`
 FROM
 `table1`
 WHERE
 `thing` = thing
);
RETURN @return_element;

I use the phpmyadmin interface.

• tadman: What are you trying to do with element=@return_element here?
• Rod: I want to return the element. Therefore maybe assign the element to the variable @return_element before return ?
• tadman: Hint: That's not an assignment, that's a comparison.

Answer 1

1) Don't declare user-defined variables.

The name of a local variable in MySQL stored program does not start with an at sign @. As an example:

 DECLARE stored_program_local_variable VARCHAR(255);
 SET stored_program_local_variable = 'somevalue';

The name of a user-defined variables start with an at sign @. (The at sign character is what distinguishes user-defined variables from other identifiers.) It's not valid to declare a user-defined variable in a stored program. To create a user-defined variable, just assign a value to it. For example:

 SET @user_defined_variable = 'somevalue';

2) If we don't need to persist variables beyond the scope of a stored program, we typically use local variables, which exist only for the duration of the stored program execution. (Which is different behavior than user-defined variables which are at the session level.)

3) Use the SELECT ... INTO syntax to retrieve scalar values into user-defined or local variables. https://dev.mysql.com/doc/refman/5.7/en/select-into.html

---

Try:

DELIMITER $$
CREATE DEFINER=`root`@`localhost`
FUNCTION `get_element_by_thing`(`thing` VARCHAR(255))
RETURNS VARCHAR(255)
... 
BEGIN
 DECLARE return_element VARCHAR(255) ;
 SELECT t1.element 
 INTO return_element 
 FROM table1 t1
 WHERE t1.thing = thing
 LIMIT 1 ;
 RETURN return_element ;
END $$
DELIMITER ;

Note: with ambiguous identifiers (i.e. routine parameter and column with the same name in a SQL statement, the routine parameter takes precedence over the column name. Qualify the column reference with the table name or table alias so it's not ambiguous. I prefer to assign routine parameters (and local variables) names that do not match column names.

If for some reason you need to assign a value to a user-defined variable in a SQL statement, you can use the := assignment operator. This is also valid outside the context of a stored program.

 SELECT @user_defined_variable := t.somecolumn
 FROM mytable t
 WHERE somecondition
 ORDER BY someexpression 
 LIMIT 1