mysql 存储过程动态执行sql 例子

DELIMITER $$;
DROP PROCEDURE IF EXISTS `set_col_value`$$
CREATE  PROCEDURE `set_col_value`
   (InTargetYear     VARCHAR(128), 
        InTargetWeek     VARCHAR(128),
        InCurrentYear    VARCHAR(1000),
        InCurrentWeek    VARCHAR(4000)
    )
BEGIN
   DECLARE sql_str TEXT;
   SET sql_str = CONCAT('INSERT INTO     res_sales_hotlead(group_id,sales_id,customer_id,year,WEEK,createuser,createtime)SELECT t.group_id,t.sales_id,t.customer_id,',InTargetYear,',',InTargetWeek,',','t.createuser,NOW() FROM res_sales_hotlead t WHERE t.year = ',InCurrentYear,' AND t.week = ',InCurrentWeek,'');
   
    SET @SQL=sql_str;
	   PREPARE s1 FROM @sql;
	   EXECUTE s1;
	   DEALLOCATE PREPARE s1;
END$$
DELIMITER ;$$

注意两个地方，@SQL 是 系统内置的 的一个函数，把要执行的动态sql 赋值给 @SQL 动态拼接的sql 要用 CONCAT 函数 连接起来 ， 如果 拼接的 sql 中的变量要添加引号 要用 CONCAT 函数处理 例如 

 SET v_sql_1= CONCAT('select count(a.concattype) from res_customer_info a left join car_staff b on b.staff_id=a.sales_id where 1=1 and a.concattype = 2 and a.auditflag = 1 and a.entitytype != 3 and UNIX_TIMESTAMP( a.createtime ) BETWEEN UNIX_TIMESTAMP(',CONCAT('"',starttime,'"'),') AND UNIX_TIMESTAMP(',CONCAT('"',endtime,'"'),')');

编译成功后call 存储过程名（参数一，参数二）;调试的时候可以把变量用 select 变量名; 的方式打印。