Solved

Not allowed to return a result set from a function.

Posted on 2014-04-03
3
3,325 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.
Comment Utility
Try changing dTotal to @dTotal.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 250 total points
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now