Why does MySQL Workbench tell me that I need a semicolon

Question

This code has passed several online checks. I do not know what is wrong. After the CONCAT function, he says that I need a semicolon, although there is already one there. And on end it says that it is an outsider when it expects the end of the instruction. What gives?

 create procedure AddColumnUnlessExists( IN dbName tinytext, IN tableName tinytext, IN fieldName tinytext, IN fieldDef text) begin IF NOT EXISTS ( SELECT * FROM information_schema.COLUMNS WHERE column_name=fieldName and table_name=tableName and table_schema=dbName ) THEN set @ddl = CONCAT('ALTER TABLE ', dbName, '.', tableName, ' ADD COLUMN ', fieldName, ' ', fieldDef); prepare stmt from @ddl; execute stmt; END IF; end; 

Answer 1

I think the problem is that you are not using DELIMITER .

So just like that:

 DELIMITER // create procedure AddColumnUnlessExists( IN dbName tinytext, IN tableName tinytext, IN fieldName tinytext, IN fieldDef text) begin IF NOT EXISTS ( SELECT * FROM information_schema.COLUMNS WHERE column_name=fieldName and table_name=tableName and table_schema=dbName ) THEN set @ddl = CONCAT('ALTER TABLE ', dbName, '.', tableName, ' ADD COLUMN ', fieldName, ' ', fieldDef); prepare stmt from @ddl; execute stmt; END IF; end // DELIMITER ; 

EDIT https://dev.mysql.com/doc/refman/5.7/en/stored-programs-defining.html

If you use the mysql client program to identify a stored program containing semicolons, a problem occurs. By default, mysql itself recognizes a semicolon as an instruction separator, so you must redefine the separator temporarily to force mysql to go through the entire stored program definition to the server.

To override the mysql delimiter, use the delimiter command. the following example shows how to do this for the dorepeat () procedure just shown. The separator is changed to // to include the entire definition should be passed to the server as a single statement, and then restored; before calling the procedure. This allows; the separator used in the body of the procedure, which should be passed to the server, and not interpreted by mysql itself.

Answer 2

The problem is the delimiter, you have to change the delimiter. I had the same problem and changing the delimiter solved the problem. See @Alex Answer.

Here is a simple explanation quoting the MySQL documentation:

The example uses the mysql client delimiter command to change the instruction delimiter; to // while the procedure is defined. This allows; the delimiter used in the procedure body, which must be passed to the server, and not interpreted by mysql itself. See Section 22.1, “Defining Saved Programs”.

https://dev.mysql.com/doc/refman/5.7/en/create-procedure.html

 DELIMITER // create procedure some_procedure() # Do what you need here END // DELIMITER ; # change the delimiter back again.