[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1141
  • Last Modified:

Not allowed to return a result set from a function

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
vijji_lakshmi
Asked:
vijji_lakshmi
1 Solution
 
Steve BinkCommented:
Just underneath your cursor declaration, you have:

select fetch_status;

That would return a result set.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now