Stored procedures (or multi-semicolon statements)

[josephbuchma]

Currently we assume that each SQL statement is delimited by ;\n

Problem

Some stuff, like stored procedures, may have multiple ;\n within one statement, for instance:

CREATE PROCEDURE my_procedure_User_Variables()
BEGIN
SET @x = 15;
SET @y = 10;
SELECT @x, @y, @x-@y;
END;

Workaround

Add dummy comment after each ; within stored procedure, e.g.:

CREATE PROCEDURE my_procedure_User_Variables()
BEGIN   
SET @x = 15; --
SET @y = 10; --
SELECT @x, @y, @x-@y; --
END;

Suggested solutions

1. Document ; -- workaround in migration file template.
2. Create new directive for enclosing arbitrary SQL as single statement.
3. ?

[gravis]

I wonder if this will only occur for CREATE PROCEDURE?
Maybe we can consider all text between CREATE PROCEDURE and END; as a single statement?
Then, for all other lines of the file, we can split by ;\n. What do you think?
Your workaround is nice, but with large procedures, it will be painful, and prevent users from copy/paste in the migration file directly.

[josephbuchma]

Hmm... That's good point. IDK if there is anything else besides stored procedures that is affected by this issue, but if there is - it should be pretty easy to cover in similar fashion.