Solved

Not allowed to return a result set from a function

Posted on 2008-09-29
3
1,132 Views
Last Modified: 2012-05-05
Hi we are migrating a procedure from mssql to mysql.
if i executed the following function on mysql iam getting "Not allowed to return a result set from a function" error.
CREATE FUNCTION TABLE_COL_LIST(xtable VARCHAR(100),includeclientid varchar(1)) RETURNS varchar(4000)
BEGIN
DECLARE colname VARCHAR(300);declare collist VARCHAR(4000);declare foundclientid VARCHAR(200);
declare cnt int default 0;
declare fetch_status int default 0;
  DECLARE table_cur CURSOR FOR
SELECT COLUMN_NAME FROM information_schema.COLUMNS C where TABLE_SCHEMA=(select database()) and table_name=xtable;
select fetch_status;
  SET @collist = '';
  IF (includeclientid IS NULL) then
  BEGIN
     SET includeclientid = 'F';
  END;
  end if;
  begin
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetch_status =-1;
   OPEN table_cur;
  FETCH FROM table_cur INTO colname;
     WHILE (fetch_status=0) do
  BEGIN
      IF NOT(colname = 'CLIENT_ID') then
begin
      IF (@collist = '') then
           SET @collist = colname;
      ELSE
        set  @collist=concat(@collist ,',',colname);
        END if;
end;
    ELSE IF (includeclientid = 'T') then
      SET foundclientid = colname;
      end if;
       END if;
     FETCH NEXT FROM table_cur INTO colname;
end;
  end while;
  CLOSE table_cur;
  end;
  IF NOT(foundclientid IS NULL) AND (includeclientid = 'T') then
set  @collist=concat(foundclientid,',',@collist);
END if;
  RETURN @collist;
END
can any one help me why iam getting this error and how do i solve it;
Regards,
vijji
0
Comment
Question by:vijji_lakshmi
[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 51

Accepted Solution

by:
Steve Bink earned 500 total points
ID: 22602624
Just underneath your cursor declaration, you have:

select fetch_status;

That would return a result set.
0

Featured Post

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
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…
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…

632 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