mysql: create procedure LOOP bug

[zundaren]

 label:
 LOOP
        FETCH cur_1 INTO
            event_id,only_code,garage,space;

        IF done THEN
            LEAVE label;
        END IF;

        set @SqlStmtA1 = concat('delete from ', eventtablename, ' where id=', event_id);
        prepare stmtA1 FROM @SqlStmtA1;
        EXECUTE stmtA1;

        COMMIT;
    END LOOP;
    CLOSE cur_1;

[nene]

Thanks for reporting.

Unfortunately the support for procedural SQL is pretty much non-existant in SQL-Formatter. Even if it would parse this code without errors, the formatted output would be pretty ugly. Specifically it would be as follows:

label: LOOP FETCH cur_1 INTO event_id,
only_code,
garage,
space;

IF done THEN
LEAVE label;

END IF;

set
  @SqlStmtA1 = concat(
    'delete from ',
    eventtablename,
    ' where id=',
    event_id
  );

prepare stmtA1
FROM
  @SqlStmtA1;

EXECUTE stmtA1;

COMMIT;

END
LOOP;

CLOSE cur_1;

At least I personally wouldn't use it for that.

But yeah, it would be better to not crash.

[fara-nak]

i'm having same issue for:

executeQuery: LOOP
        FETCH something
        Do something
END LOOP executeQuery;

is there any work around or have to uninstall prettier for sql entirely to get rid of this crash? @nene

[nene]

One pretty ugly workaround is to use MySQL conditional comments:

/*! executeQuery: */ LOOP
        FETCH something
        Do something
END LOOP executeQuery;