Solved

Not allowed to return a result set from a function.

Posted on 2014-04-03
3
3,599 Views
Last Modified: 2014-04-04
Hi Experts,

 Can anyone tell me why I keep getting the following  error, when my goalis to return a single value?

error 1415 (0A000): Not allowed to return a result set from a function.

My code
 DELIMITER $$

 CREATE FUNCTION get_author_cost(auth_id SMALLINT(5)) RETURNS DECIMAL(10,2)

 BEGIN

 DECLARE dTotal DECIMAL(10,2);

 SELECT @dTotal:=SUM(price) 
 FROM books
 WHERE author = auth_id;

 RETURN dTotal;

 END $$


 DELIMITER ;

Open in new window

0
Comment
Question by:APD_Toronto
3 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 39977953
Try changing dTotal to @dTotal.
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
ID: 39978001
actually, I would suggest to do the other way round, and not use @, as it's for session-level variables ...
DELIMITER $$

 CREATE FUNCTION get_author_cost(auth_id SMALLINT(5)) RETURNS DECIMAL(10,2)

 BEGIN

 DECLARE dTotal DECIMAL(10,2);

 SELECT SUM(price)  
     INTO dTotal
 FROM books
 WHERE author = auth_id;

 RETURN dTotal;

 END $$
 DELIMITER ;

Open in new window

0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 250 total points
ID: 39978128
In the function, you can use syntax select column into variable, but not select variable:=column. Even though the result is the same, mysql expects select with into. From documentation:

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Statements that return a result set can be used within a stored procedure but not within a stored function. This prohibition includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE

So, when it encounters select without into, it generates an error.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question