I can't seem to declare variables inside this procedure

Question

I can't get this code to work, i keep getting syntax errors, and i don't see anything wrong with the declare sentences.

    delimiter //
    create procedure Plsql1 (Count int)
        begin


            CREATE TABLE alumnos(
                nombre VARCHAR(7) primary key,
                edad INT (7),
                sexo INT (2));

            DECLARE Count int default 10;
            DECLARE Number int default 0;
            DECLARE done int = 0;

            etiq1: loop
                if not done then
                    INSERT INTO alumnos VALUES(CONCAT('Victor',Number), Number*5, Number%2);
                    SET N=N+1;

                    if (Number=Count) SET done=1;
                else
                    leave etiq1;
                end if;
            end loop;

            SELECT * FROM alumnos;
        end //
delimiter ;

When i try to get this to work, i get this:

ERROR 1064 (42000): 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 Count int default 10;
        DECLARE Number int default 0;
        DECL' at line 10

However, i can't see any syntax errors in there. Some advice for this, please?

Answer 1

You can create the table like that in the stored procedure:

CREATE PROCEDURE procedure1(IN tableName VARCHAR(255))
BEGIN
  SET @sql = CONCAT('CREATE TABLE ', tableName, '(column1 INT(11))');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  // Do all other stuff then...
END

That should work.