Return value from Function in MySQL

Question

Hi I try to write a function in MySQL, which gives me back a value from a table.

I'm getting the error:

Error Code: 1415. Not allowed to return a result set from a function

I know what it means, but I don't know how to solve the problem. I just need one value, the Id of the inserted row. I'm glad for any help.

What the function does is:
Insert a new row into the aip_request table, with a Random value, that way I can identify the row after the insert. The function should return the Id of the created line. The request_id is an autovalue.

DROP FUNCTION IF EXISTS `sp_get_new_request_id`;
DELIMITER $$

CREATE FUNCTION `sp_get_new_request_id` ()
RETURNS BIGINT
BEGIN
 DECLARE var_random bigint;
 SET @random := CAST(RAND(NOW()) AS CHAR(150));
 INSERT INTO aip_request
  (Firstname)
  VALUES( @random );
 SELECT request_id INTO var_random FROM aip_request
 WHERE Firstname = @random 
 LIMIT 1;
 Return var_random;
END
$$

Devart · Accepted Answer · 2012-02-02 10:17:29Z

1

Change this query -

SELECT @random := CAST(RAND(NOW()) AS CHAR(150));

with this one -

SET @random := CAST(RAND(NOW()) AS CHAR(150));

...you cannot execute SELECT query that returns data-set from the stored function.

answered Feb 2, 2012 at 10:17

Devart

120k23 gold badges172 silver badges189 bronze badges

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

Collectives™ on Stack Overflow

Return value from Function in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related