CODEWITHSUNDEEP
mysqlsqlfunctionreturn-valuestored-functions
chris
chris

Reputation: 565

Return value from Function in MySQL

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

Upvotes: 0

Views: 4413

Answers (1)

Devart
Devart

Reputation: 121902

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.

Upvotes: 1

Related Questions