MySQL Procedure Syntax Error "Missing END"

Question

I have to following procedure trying to dynamically create a view.

CREATE DEFINER=`root`@`%` PROCEDURE `uspCreateViewFromTable`(IN ViewName varchar(255), IN TableName varchar(255))
BEGIN
#View Droppen falls sie schon erstellt wurde
SET @s = CONCAT('DROP VIEW IF EXISTS ',ViewName);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


# Verwendete Spalten filtern und Statement bauen
#SET @columns = CAST('SELECT ' AS VARCHAR(10));

DECLARE column varchar(500);
DECLARE column_cursor FOR SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = Tablename;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
OPEN column_cursor

read_loop: LOOP
    FETCH column_cursor INTO column
    # do something
    SELECT column;
    IF done THEN
      LEAVE read_loop;
    END IF;
END LOOP;
CLOSE column_cursor;

END

I get the Error "Missing END" and I have no idea why.

The syntax checker underlines the semikolon at the end of Line

DEALLOCATE PREPARE stmt;

When I move the dealloc to the end the syntac checker highlightes the semikolon at the line before.

EXECUTE stmt;

If I remove everthing after the dealloc it works.

Answer 1

Some problems:

• DECLARE is permitted only inside a BEGIN ... END compound statement and must be at its start, before any other statements. See 13.6.3 DECLARE Syntax.
• ERROR 1193 (HY000): Unknown system variable 'done'.
• Check the syntax of cursors. See 13.6.6.2 Cursor DECLARE Syntax.
• column is keyword and reserved word. See 9.3 Keywords and Reserved Words.
• Missing some ;.

DELIMITER //

CREATE PROCEDURE `uspCreateViewFromTable`(
  IN ViewName varchar(255),
  IN TableName varchar(255)
)
BEGIN
  /*
  #View Droppen falls sie schon erstellt wurde
  SET @s = CONCAT('DROP VIEW IF EXISTS ',ViewName);
  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
  */

  # Verwendete Spalten filtern und Statement bauen
  #SET @columns = CAST('SELECT ' AS VARCHAR(10));

  -- DECLARE column varchar(500);
  DECLARE `column` varchar(500);
  DECLARE done BOOL DEFAULT FALSE;

  /*
  DECLARE column_cursor FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = Tablename;
  */
  DECLARE column_cursor CURSOR FOR
  SELECT column_name
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE table_name = Tablename;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  #View Droppen falls sie schon erstellt wurde
  SET @s = CONCAT('DROP VIEW IF EXISTS ',ViewName);
  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;

  -- OPEN column_cursor
  OPEN column_cursor;

  read_loop: LOOP
      -- FETCH column_cursor INTO column
      FETCH column_cursor INTO `column`;
      # do something
      -- SELECT column;
      SELECT `column`;
      IF done THEN
        LEAVE read_loop;
      END IF;
  END LOOP;
  CLOSE column_cursor;
END//

DELIMITER ;