MySQL stored Procedure error with IF...THEN...END IF; statements

Question

I have a MySQL stored procedure that throws an error.

DELIMITER $$

DROP PROCEDURE IF EXISTS `test_schema`.`TEST_SPROC` $$  
CREATE PROCEDURE `test_schema`.`TEST_SPROC` (IN var0 INT, var1 INT)  
BEGIN

  DECLARE var0 INT;
  DECLARE var1 INT;

  SELECT COUNT(*) INTO var0 FROM INFORMATION_SCHEMA.`TABLES` 
  WHERE `TABLE_SCHEMA`='test_schema' AND `TABLE_NAME`='original_table'; 

  SELECT COUNT(*) INTO var1 FROM INFORMATION_SCHEMA.`COLUMNS` 
  WHERE `TABLE_SCHEMA`='test_schema' AND `TABLE_NAME`='new_table' 
  AND `COLUMN_NAME`='id';

  IF var0=1 THEN
    RENAME TABLE test_schema.original_table TO test_schema.new_table;   
  END IF;

  IF var1=1 THEN
    ALTER TABLE test_schema.new_table CHANGE id AccountID VARCHAR;   

  END IF;       #error is thrown here.

END $$

DELIMITER ;

Error:

Script line: 4 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 IF;

END' at line 15

What is wrong with my if statement?

Your if statement is correctly formed. It's the alter table statement that has syntax errors. — Eric Leschinski
– Eric Leschinski, Commented Dec 7, 2013 at 5:24

Tom Mac · Accepted Answer · 2013-07-09 10:01:42Z

1

Change this line:

ALTER TABLE test_schema.new_table CHANGE id AccountID VARCHAR;

to this:

ALTER TABLE test_schema.new_table CHANGE id AccountID VARCHAR(100);

Of course you should specify a length for the VARCHAR column that is appropriate. I've just used VARCHAR(100) as an example.

answered Jul 9, 2013 at 10:01

Tom Mac

9,8633 gold badges32 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Eric Leschinski · Accepted Answer · 2013-12-18 04:51:43Z

0

You have to specify a length for the alter table statement. See the comment below:

IF varTable=1 THEN
    RENAME TABLE test_schema.original_table TO test_schema.new_table;
    SELECT COUNT(*) INTO varColumn FROM INFORMATION_SCHEMA.`COLUMNS` 
    WHERE `TABLE_SCHEMA`='test_schema' AND `TABLE_NAME`='new_table' 
    AND `COLUMN_NAME`='id';

    IF varColumn=1 THEN
      #The following statement must be "VARCHAR(255)"  not just "VARCHAR"
      ALTER TABLE test_schema.new_table CHANGE id AccountID VARCHAR(255);
    ELSE
      #statements.
    END IF;
END IF;

edited Dec 18, 2013 at 4:51

answered Dec 7, 2013 at 5:22

Eric Leschinski

155k96 gold badges423 silver badges337 bronze badges

Collectives™ on Stack Overflow

MySQL stored Procedure error with IF...THEN...END IF; statements

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related