CODEWITHSUNDEEP
mysql
joedborg
joedborg

Reputation: 18353

MySQL select in function

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)

Upvotes: 11

Views: 62295

Answers (5)

Himalaya Garg
Himalaya Garg

Reputation: 1609

DELIMITER $$

USE `mydatabase`$$

DROP FUNCTION IF EXISTS `fnGetActiveEventId`$$

CREATE DEFINER=`mydbuser`@`%` FUNCTION `fnGetActiveEventId`() RETURNS INT(11)
BEGIN   
    SET @eventId = (SELECT EventId FROM `Events` WHERE isActive=1 ORDER BY eventId DESC LIMIT 1);   
    
    RETURN @eventId;
    END$$

DELIMITER ;

Upvotes: 1

user6079391
user6079391

Reputation: 11

You have a error in your code, if you need only a result, you obtain it from a simple select an assign with the word INTO like this, remember, it return the last one.

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$$

Upvotes: 1

Geoduck
Geoduck

Reputation: 9001

You have a table named table? Escape that name if that is really what it is:

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

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

Upvotes: 0

Edouard B
Edouard B

Reputation: 316

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$$

Upvotes: 12

Zohaib
Zohaib

Reputation: 7116

try to specify the size of char return type. for example if name can be of 20 characters then try

RETURNS CHAR(20)

Upvotes: 4

Related Questions