How to create and execute procedures in MySQL workbench

Question

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...

Answer 1

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.