How to create and execute procedures in MySQL workbench

mysql, mysql-error-1064

Solution

Have you ended the entire query? Try setting a delimiter, and use it after the END so the server knows you 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 the by

While `DELETE FROM TABLE` does remove all data from the table, `TRUNCATE table` does so faster. Unless you have good reasons to use `DELETE` (they exist), `TRUNCATE` might be what you want.

Problem

I created a Spatial table Points using SQL Editor in MySQL workbench. To fill this table, the following is the code I am using. ``` 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 the error Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END' at line 1 Executing the statement `CALL fill_points(1000);` shows the same error I even don't know whether the way I proceed is correct or not. Can anybody help me...

Original source