How do you use user variables in Mysql If clause?

Question

I want to alter a table and add a column to it if it doesn't already exist. So I populate a variable @row and if it's I want to alter the able as so.

SET @row = (SELECT count(*) FROM information_schema.COLUMNS WHERE TABLE_NAME='tblname' AND COLUMN_NAME='colname' and table_schema='dbname');   
IF(@row <1) THEN 
   ALTER TABLE tblname ADD colname INT;  
END IF;

But I am getting a syntax error

#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 'IF(@row <1) THEN ALTER TABLE tblname ADD colname INT' at line 1

What am I doing wrong?

Answer 1

In MySQL you need a shell around flow statements like if.

Put a procedure around it and it will work.

Answer 2

You can use prepared statements

IF (@row <1) THEN 
  SET @lSQL = "ALTER TABLE tblname ADD colname INT";
  PREPARE stmt FROM @lSQL;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END IF;

Hope it helps.