Search code examples
mysqlfunctionprocedure

Can i create a procedure or fuction to drop a parameter table in mysql?

delimiter //
DROP PROCEDURE IF EXISTS drop_table//
create procedure drop_table(in table_name varchar) #创建带参数的存储过程
begin
        drop table if EXISTS table_name;
end//
delimiter ;

call drop_table('a')

i use Navicat to create a procedure to drop a parameter table in mysql.

Error:Invalid Stored Procedure Syntax

Solution

  • As commented by P.Salmon, you cannot pass substitute the procedure argument (ie a variable) directly in a sql statement. You need dynamic sql: build the query as a string, then execute it.

    delimiter //

create procedure drop_table(in p_table_name varchar(200))
begin
    set @q = concat('drop table if exists ', p_table_name);
    prepare stmt from @q;
    execute stmt;
    deallocate prepare stmt;
end//

delimiter ;

    Demo on DB Fiddle

    Note: the varchar datatype needs a length.