Stored function in MYSQL

Question

If I have a TABLE named MyTable which has columns say C1(type date) and C2 (type character) I want to create a stored function that takes an input and the input should always belong to C1, and the output of the stored function should be the corresponding element in C2. I have tried to do it using the 'select' statement followed by 'where' clause inside the stored function but was not able to achieve it. Is there any other way to accomplish this task.

DELIMITER $$ 
CREATE FUNCTION `MyFunction` 
  (`Date` datetime) 
  RETURNS char(10)
BEGIN 
  DECLARE MyVariable char(10)
SELECT MyVariable = `C2`
FROM MyTable
WHERE `Date` = `C1`; RETURN MyVariable;
END$$
DELIMITER ;

But this keeps giving me ERROR CODE: 1064

Answer 1

At first glance, I see a syntax error:

...
BEGIN 
  DECLARE MyVariable char(10)     <-- needs a semicolon here
  SELECT MyVariable = `C2`
  ...

Every statement within the body of your routine must end with a semicolon. See examples of DECLARE in this manual page: https://dev.mysql.com/doc/refman/8.0/en/local-variable-scope.html

It should be like this:

...
BEGIN 
  DECLARE MyVariable char(10);
  SELECT MyVariable = `C2`
  ...

---

Re your comment:

Error 1415 means "cannot return a result set". Your stored function is doing a SELECT without putting the result into your declared local variable using an INTO keyword.

You appear to be trying to set the value of MyVariable using = but that's just making a comparison. It doesn't assign anything to MyVariable.

Without using INTO to assign the variable, your SELECT statement is by default returning a result set. This is allowed in a stored procedure, but not in a stored function. A stored function must return a single scalar value, not a result set.

...
BEGIN 
  DECLARE MyVariable char(10);

  SELECT `C2` INTO MyVariable
  FROM MyTable
  WHERE `Date` = `C1`;

  RETURN MyVariable;
END

---

P.S.: I edited your question to replace the term "user-defined function" with "stored function". These are two different things in MySQL. You are writing a stored function.

In MySQL, they use the term user-defined function (UDF) for a function you implement in C/C++ code and compile into the MySQL server. It's less common for developers to write this type of extension.