MySQL select in function

Question

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)

this might be obvious but is there a row in table with id = 1 ? — Zohaib
– Zohaib, Commented Nov 23, 2011 at 10:22
when I run the select on its own, it comes back with a result. — joedborg
– joedborg, Commented Nov 23, 2011 at 10:27
You forgot to tell us what the expected result was, or what the table schema is, or what its contents look like. — Lightness Races in Orbit
– Lightness Races in Orbit, Commented Nov 23, 2011 at 10:45

Edouard B · Accepted Answer · 2011-11-23 11:07:12Z

12

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

answered Nov 23, 2011 at 11:07

Edouard B

3161 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zohaib · Accepted Answer · 2011-11-23 10:43:21Z

4

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

RETURNS CHAR(20)

answered Nov 23, 2011 at 10:43

Zohaib

7,1263 gold badges29 silver badges35 bronze badges

1 Comment

joedborg Over a year ago

Thanks!! Thought that just setting CHAR would be automatic.

user6079391 · Accepted Answer · 2016-03-17 20:44:07Z

1

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

answered Mar 17, 2016 at 20:44

user6079391

111 bronze badge

Comments

Himalaya Garg · Accepted Answer · 2021-08-16 10:22:40Z

1

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 ;

answered Aug 16, 2021 at 10:22

Himalaya Garg

1,66921 silver badges24 bronze badges

Comments

Geoduck · Accepted Answer · 2014-09-09 05:01:59Z

0

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

answered Sep 9, 2014 at 5:01

Geoduck

9,0212 gold badges29 silver badges27 bronze badges

Collectives™ on Stack Overflow

MySQL select in function

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related