How to create and execute procedures in MySQL Workbench

Question

I created spatial table points using the SQL editor in MySQL workbench. To populate this table, the following code that I use.

CREATE PROCEDURE fill_points( IN size INT(10) ) BEGIN DECLARE i DOUBLE(10,1) DEFAULT size; DECLARE lon FLOAT(7,4); DECLARE lat FLOAT(6,4); DECLARE position VARCHAR(100); -- Deleting all. DELETE FROM Points; WHILE i > 0 DO SET lon = RAND() * 360 - 180; SET lat = RAND() * 180 - 90; SET position = CONCAT( 'POINT(', lon, ' ', lat, ')' ); INSERT INTO Points(name, location) VALUES ( CONCAT('name_', i), GeomFromText(position) ); SET i = i - 1; END WHILE; END 

when I executed it, it shows an error

Error code: 1064. You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax for use next to "END" on line 1

Statement execution

CALL fill_points(1000);

shows the same error

I don’t even know if it’s right or wrong.

Can someone help me ...

Answer 1

Have you finished the entire request? Try setting a separator and use it after END so that the server knows that you have finished the command.

 delimiter // CREATE PROCEDURE fill_points( IN size INT(10) ) BEGIN DECLARE i DOUBLE(10,1) DEFAULT size; DECLARE lon FLOAT(7,4); DECLARE lat FLOAT(6,4); DECLARE position VARCHAR(100); -- Deleting all. DELETE FROM Points; WHILE i > 0 DO SET lon = RAND() * 360 - 180; SET lat = RAND() * 180 - 90; SET position = CONCAT( 'POINT(', lon, ' ', lat, ')' ); INSERT INTO Points(name, location) VALUES ( CONCAT('name_', i), GeomFromText(position) ); SET i = i - 1; END WHILE; END // delimiter ; 

Also by

While DELETE FROM TABLE deletes all data from the table, the TRUNCATE table makes it faster. Unless you have good reason to use DELETE (they exist), TRUNCATE may be what you want.