mySql drop column if not exist

Question

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;

Answer 1

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]>