Solved

Not allowed to return a result set from a function.

Posted on 2014-04-03
3
3,696 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL several years ago, it seemed like now was a good time to update it for object-oriented PHP.  This article does that, replacing as much as possible the pr…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

695 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