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
Solved

Not allowed to return a result set from a function.

Posted on 2014-04-03
3
3,537 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

Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

838 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