What am I doing wrong in this MySQL stored procedure?

Question

I am trying to use the following stored procedure.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` 
PROCEDURE `DeleteField`( IN _TABLENAME Text, IN _FIELDNAME text)
BEGIN
  if exists (select * from information_schema.Columns 
    where table_name = _TABLENAME and column_name = _FIELDNAME) 
  then 
    alter table _TABLENAME drop column _FIELDNAME;
  end if;
END

So, I call ("anytable", "Anyfield") and I get an error message Error code: 1146Table'Database._tablename'doesn't exist This _tablename should be my parameter, not a string.

I need help before I hang myself, I love my life too much.

Answer 1

I expect that for this you will need to create a dynamic SQL query.

An example of how to do this:

http://www.java2s.com/Code/SQL/Procedure-Function/Createadynamicstatementinaprocedure.htm

, .

    DECLARE l_sql VARCHAR(4000);
    SET l_sql=CONCAT_ws(' ',
                'ALTER table ',_TABLENAME,' drop column ',_FIELDNAME);
    SET @sql=l_sql;
    PREPARE s1 FROM @sql;
    EXECUTE s1;
    DEALLOCATE PREPARE s1;