MySql error assigning variables

Question

I'm trying to write a script that will create smaller tables from a large parent tables. (I have no choice in this case.) However, it's been a while since I've had to do any serious SQL work and I'm stumped by what is probably something stupid... This won't get past the first couple of lines without blowing up.

DELIMITER $$
BEGIN
SET @I = 1; 
SET @CountCol = 'Item Number';
SET @StartPos = 0;
SET @EndPos = 24999;
SET @TotalCount = (SELECT Count(@CountCol) FROM `All_Info`);
SET @HowMany = CEILING(@TotalCount / 25000) + 1;

WHILE @I < @HowMany DO
    SET @query = CONCAT('CREATE TABLE All_Info_',@I,' AS (SELECT @n := @n + 1 `id`, * FROM All_Info LIMIT ',@StartPos,',',@EndPos,'),PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1;)');
    PREPARE stmt from @query;
    EXECUTE stmt;

    SET @I = @I + 1;
    SET @StartPos = @EndPos;
    SET @EndPos = @EndPos + 25000;
END WHILE;
END$$

The error I get is:

[ERROR in query 1] 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 'SET @I = 1' at line 2

I feel like I'm taking crazy pills. Any ideas what I'm doing wrong?

(Note: using Sequel Pro as my client, in case that is somehow relevant)

Answer 1

My answer, as usual, was something obvious: you can't do a WHILE loop outside of a stored procedure. Whoops.

Placing this into a procedure and calling it fixed my woes. I also had to remove the engine and default charset. Simplified a bit. Works great now.

DROP PROCEDURE IF EXISTS TableSplitTemp;
DELIMITER ;;
create procedure TableSplitTemp ()
BEGIN
SET @I = 1;
SET @CountCol = 'Item Number';
SET @StartPos = 0;
SET @EndPos = 24999;
SET @TotalCount = (SELECT Count(@CountCol) FROM `All_Info`);
SET @HowMany = CEILING(@TotalCount / 25000) + 1;
WHILE @I < @HowMany DO
    SET @query = CONCAT('CREATE TABLE All_Info_',@I,' AS (SELECT * FROM All_Info LIMIT ',@StartPos,', ',@EndPos,');');
    PREPARE stmt from @query;
    EXECUTE stmt;

    SET @I = @I + 1;
    SET @StartPos = @EndPos;
    SET @EndPos = @EndPos + 25000;
END WHILE;
END;;
delimiter ;
/* call TableSplitTemp(); ta-daa it works! */