How can I access MySQL Procedure arguments?

Question

I'm trying to make a procedure that will create a column in a given table if it doesn't exist. The name of the new column should be taken from the argument I have called "colname".

However this doesnt work, it creates the column, but with the actual name "colname", not the value I call it with. What am I doing wrong here?

delimiter ;;
create procedure autoColumns (colname varchar(64))
begin
    declare continue handler for 1060 begin end;
    alter table affiliates add colname DECIMAL(5,2);
end;;
call autoColumns('testColumn');;

score 1 · Answer 1 · answered Oct 16 '15 at 19:50

In mysql variables cannot be used to dinamically determine table or column names, only values. To achieve what you want you need to assemble the sql as string and execute it dinamically using prepared statement. See 3rd example in the linked documentation.

score 1 · Accepted Answer · edited May 23 '17 at 11:44

You are pretty close, OP.

create table affiliates (id int);

delimiter //
create procedure autoColumns(colname varchar(64))
begin
    set @dml = concat('alter table affiliates add column ', colname, ' decimal(5,2)');
    prepare stmt from @dml;
    execute stmt;
end
//
delimiter ;

call autoColumns('test2');  

select * from affiliates;  -- you'll see ID and test2 columns

That should do it for you. Also see this question: How To have Dynamic SQL in MySQL Stored Procedure

How can I access MySQL Procedure arguments?

2 Answers2