vijji_lakshmi
asked on
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),includeclient id 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(foundclien tid,',',@c ollist);
END if;
RETURN @collist;
END
can any one help me why iam getting this error and how do i solve it;
Regards,
vijji
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),includeclient
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
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(foundclien
END if;
RETURN @collist;
END
can any one help me why iam getting this error and how do i solve it;
Regards,
vijji
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.