mySql drop column if not exist
I am trying to write a down migration SQL code and I wrote the following code. It does not work and I get an error saying:
Can’t DROP ‘column_name_arg’; check that column/key exists
Which means I am not using the argument variable.
I appreciate any help or hint to fix this and feedback to improve or simplify it. I am also wondering should this be a procedure or prepared statement?
drop procedure if exists schema_change; delimiter ';;' create procedure schema_change(in table_name_arg VARCHAR(40), in column_name_arg VARCHAR(40)) begin if exists(select * from information_schema.columns where table_schema = schema() and table_name = table_name_arg and column_name = column_name_arg) then alter table TestResults drop column column_name_arg; end if; end;; delimiter ';' call schema_change('TestResults', 'minScore'); call schema_change('TestResults', 'maxScore'); drop procedure if exists schema_change; 
You can use a prepared statement to do this. The column name can’t be a variable.
Create procedure:
drop procedure if exists schema_change; delimiter // create procedure schema_change(in table_name_arg VARCHAR(40), in column_name_arg VARCHAR(40)) begin if exists(select * from information_schema.columns where table_schema = schema() and table_name = table_name_arg and column_name = column_name_arg) then SELECT CONCAT('ALTER TABLE TestResults drop column ',column_name_arg) INTO @sqlv; PREPARE stmt FROM @sqlv; EXECUTE stmt; DEALLOCATE PREPARE stmt; end if; end; // delimiter ; Show table before:
MariaDB [bernd]> desc TestResults; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | x | int(11) | YES | | NULL | | | y | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) Call procedure:
MariaDB [bernd]> call schema_change('TestResults', 'y'); Query OK, 0 rows affected (0.03 sec) Show table after:
MariaDB [bernd]> desc TestResults; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | x | int(11) | YES | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.00 sec) MariaDB [bernd]> 
