Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL select in function

Tags:

mysql

I'm trying to write a MySQL function with a select 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)
like image 890
joedborg Avatar asked Nov 23 '11 10:11

joedborg


1 Answers

Does it works 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$$
like image 184
Edouard B Avatar answered Sep 30 '22 18:09

Edouard B