How do I convert this MSSQL cursor function into MYSQL

Question

I am trying to convert a MSSQL function into MySQL. Here is what I've done so far. Can someone please take a look at it ?

ALTER FUNCTION [dbo].[GetCommaDelimitedCategoryIDs]
(
    @datafeedcategoryinfoid int
)
RETURNS varchar(2000)
AS
BEGIN

DECLARE @category_list varchar(4500), @categoryid varchar(20)
SET @category_list = ''

  DECLARE category_cursor CURSOR 
  LOCAL FAST_FORWARD FOR 
  SELECT DISTINCT categoryid
  FROM category_mapping
    WHERE datafeedcategoryinfoid = @datafeedcategoryinfoid

  OPEN category_cursor;

  FETCH NEXT FROM category_cursor
  INTO @categoryid;

  WHILE @@FETCH_STATUS = 0
  BEGIN

    SET @category_list = @category_list + @categoryid + ','

    FETCH NEXT FROM category_cursor
    INTO @categoryid;

  END

  CLOSE category_cursor;
  DEALLOCATE category_cursor;

  IF (LEN(@category_list) > 0)
    SET @category_list = SUBSTRING(@category_list, 1, LEN(@category_list) - 1)

  RETURN @category_list


END

And here is the MySQL function

DELIMITER $$

CREATE DEFINER=``@`` FUNCTION `GetCommaDelimitedCategoryIDs`(
    p_datafeedcategoryinfoid int
) RETURNS varchar(2000) CHARSET latin1
BEGIN

DECLARE v_category_list varchar(4500);
DECLARE v_categoryid varchar(20);
DECLARE category_cursor CURSOR FOR 
    SELECT DISTINCT categoryid 
    FROM category_mapping 
    WHERE datafeedcategoryinfoid = p_datafeedcategoryinfoid;

    SET v_category_list = '';
    OPEN category_cursor;

    myloop: LOOP
    FETCH category_cursor INTO v_categoryid;
    IF done THEN
        LEAVE myloop;
    END IF;
    SET v_category_list = Concat(v_category_list , v_categoryid , ',');
    FETCH category_cursor INTO v_categoryid;

    END LOOP;

  CLOSE category_cursor;


  IF (CHAR_LENGTH(RTRIM(v_category_list)) > 0) THEN
    SET v_category_list = SUBSTRING(v_category_list, 1, CHAR_LENGTH(RTRIM(v_category_list)) - 1);
  END IF;

  RETURN v_category_list;


END

But the above function doesn't return the value. Mysql Workbench doesn't return any error, how do i debug ?

Answer 1

Isn't this a job for group_concat()? Something like:

select group_concat(distinct categoryid order by categoryid SEPARATOR ',')
from category_mapping 
where datafeedcategoryinfoid = p_datafeedcategoryinfoid;

Here is a demo of group_concat().