Search code examples
mysqlstored-procedurescursorstring-concatenation

How to create a comma separated value using Cursor of MySQL Stored Procedure?

I want to formulate a variable from a cursor with a final value something like: "1,2,3". Following is the simple stored proc:

    OPEN cursor1;
    BEGIN
        LOOP1: LOOP
        FETCH cursor1 INTO main_account_id;
        IF no_more_rows THEN
            CLOSE cursor1;
            LEAVE LOOP1;
        END IF;
        // Here I want to achieve something like "1,2,3" from main_account_id
        END LOOP LOOP1;
    END;
Solution

  • First define your variable as NULL:

    SET @comma_sep_value = NULL;

    Then use CONCAT_WS() in the loop:

    SET @comma_sep_value = CONCAT_WS(',', @comma_sep_value, @next_element);

    There will be no trailing comma, so no TRIM will be required.