MySQL selection in function

Question

I am trying to write a MySQL function with a choice inside, but always get a NULL return

CREATE FUNCTION test (i CHAR) RETURNS CHAR NOT DETERMINISTIC BEGIN DECLARE select_var CHAR; SET select_var = (SELECT name FROM table WHERE id = i); RETURN select_var; END$$ mysql> SELECT test('1')$$ +-----------------+ | test('1') | +-----------------+ | NULL | +-----------------+ 1 row in set, 1 warning (0.00 sec) mysql> mysql> mysql> SHOW WARNINGS -> $$ +---------+------+----------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------+ | Warning | 1265 | Data truncated for column 'i' at row 1 | +---------+------+----------------------------------------+ 1 row in set (0.00 sec) 

Answer 1

try specifying the size of the char return type. for example, if the name can be 20 characters, try

 RETURNS CHAR(20) 

Answer 2

Does this work with this:

 CREATE FUNCTION test (i CHAR) RETURNS VARCHAR(SIZE) NOT DETERMINISTIC BEGIN DECLARE select_var VARCHAR(SIZE); SET select_var = (SELECT name FROM table WHERE id = i); RETURN select_var; END$$ 

Answer 3

You have a mistake in the code, if you only need the result, you will get it from a simple choice to assign the word INTO , like this, remember that it returns the last.

 CREATE FUNCTION test (i CHAR) RETURNS VARCHAR(SIZE) NOT DETERMINISTIC BEGIN DECLARE select_var VARCHAR(SIZE); SELECT name INTO select_var FROM table WHERE id = i; RETURN select_var; END$$ 

Answer 4

Do you have a table called table? Remove this name if it is true:

 (SELECT name FROM `table` WHERE id = i); 

or put the table name in ... seems to be missing