动态SQL 的应用
BEGIN
set @tempsql = CONCAT(
‘SELECT ei11.* FROM ‘,i_type_name,’ ei11
LEFT JOIN view_c_ei_enterpriseinfo ei01 ON ei01.ENTERPRISE_ID = ei11.ENTERPRISE_ID
WHERE 1=1’
);
IF i_enterprise_name IS NOT null and i_enterprise_name <> '' THEN
set @tempsql = CONCAT(@tempsql, ' and ei01.enterprise_name like ''', CONCAT('%',i_enterprise_name,'%'), '''');
end IF;
set @tempsql = CONCAT(@tempsql, ' and ei01.found_date >= ''', i_found_date_start, ''''); -- 成立日期
set @tempsql = CONCAT(@tempsql, ' and ei01.found_date <= ''', i_found_date_end, ''''); -- 成立日期
– SELECT @tempsql;
/这里可以写上select * from @tempsql 来检查你的SQL拼接是否正确,运行时把它注释就好。但是如果字符串太长的话,可能就看不到了/
prepare stmt from @tempsql;-- 预编译SQL
EXECUTE stmt; – 执行SQL – 执行SQL
END