Mistake in SQL query

Question

I have a query which should add random data to my table, but it doesn't work.

My query looks like this:

DECLARE @i INT;
SET @i = 0;
WHILE (@i < 25000) DO
BEGIN
INSERT INTO ak_class (class_name, class_description)
VALUES (CONCAT('Rose', RAND()*250000),
CONCAT('Roses are awesome', RAND()*250000));
SET @i = @i + 1;
END WHILE;

The error that is shown:

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 'DECLARE @i INT' at line 1

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

(0 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

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 'WHILE (@i < 25000) DO
BEGIN
INSERT INTO ak_class (class_name, class_description)' at line 1

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

(0 row(s) affected)
Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

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 WHILE' at line 1

Execution Time : 00:00:00:000
Transfer Time  : 00:00:00:000
Total Time     : 00:00:00:000

https://dev.mysql.com/doc/refman/5.0/en/declare-local-variable.html — Marc B, May 28 '15 at 14:35
related: https://stackoverflow.com/questions/11754781/declare-a-variable-in-mysql — TZHX, May 28 '15 at 14:42
You can't do declare/while unless you're defining a stored procedure. — Marc B, May 28 '15 at 14:48

score 0 · Accepted Answer · answered May 28 '15 at 15:00

MySQL does not support the execution of anonymous blocks of stored procedure code.

You need to create a stored procedure including that code and then invoke it.

Create the procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS insert_random_rows $$

CREATE PROCEDURE insert_random_rows () 
BEGIN
DECLARE @i INT;
SET @i = 0;
WHILE (@i < 25000) DO
BEGIN
INSERT INTO ak_class (class_name, class_description)
VALUES (CONCAT('Rose', RAND()*250000),
CONCAT('Roses are awesome', RAND()*250000));
SET @i = @i + 1;
END WHILE;
END $$
DELIMITER ;

You need to call it now:

CALL insert_ten_rows();

This should work for you.

Mistake in SQL query

1 Answers1