MySQL stored procedure writing-Dynamic parameter application

--Deletedrop procedure if exists up_common_select

--CreateCREATE　PROCEDURE `up_common_select`
(
in t_name varchar(50)
)
begin
declare v_sql varchar(500);
set v_sql= concat('select * from ',t_name);
select v_sql;
--注意:prepare(预处理)execute stmt using @var,只能跟@var变量,declare和传入的变量不行!!!
set @v_sql=v_sql;
prepare stmt from @v_sql;
EXECUTE stmt ;
deallocate prepare stmt;
end;

--Callcall up_common_select('admin_authority');

Attention matters

1 mysql5.0.13 supports calling prepare in stored procedures

2 Prepare stmt from ' select * '; Wrong

Mysql5.0.24,prepare does not support table name to do variable!

Solution: Combine strings with the Contat () function

3 Execute stmt [using @var, @var2]

Must be a variable in the form of @var, parameter variable passed in, declare variable not

4. deallocate prepare stmt; Explicit release of Prepare, if not released, MySQL will be released!