在存储过程中谢了个动态sql sql 是由传入的参数拼接成的 除了预处理还有木有其他的办法能让此sql语句在存储过程中执行虽然这样能出结果 但是觉得欠妥
BEGINDECLARE catalogID VARCHAR(10000);#100004DECLARE storageID VARCHAR(100);#100000DECLARE startDatre VARCHAR(30);#'2011-09-13 0:00:00' DECLARE endDatre VARCHAR(30);#' '2011-09-18 23:59:59'DECLARE dateStr VARCHAR(10000);#' '2011-09-18 23:59:59'SET catalogID=catalogID_;SET storageID=storageID_;SET startDatre=startDatre_;SET endDatre=endDatre_;CREATE TEMPORARY table IF NOT EXISTS temp_plan_and_stock
select p.catalog_id ,c.title as catalog_title ,p.plan_storage_id, s.title as storage_title, left(p.delivery_date,10) as delivery_date ,
sum(p.quantity) as quantity from plan_storage_sales p,product_catalog c,product_storage_catalog s
where find_in_set (p.catalog_id,product_line_child_list(catalogID) ) and p.catalog_id = c.id and p.plan_storage_id = s.id
and p.plan_storage_id=storageID and p.delivery_date between startDatre and endDatre
group by p.catalog_id, left(p.delivery_date,10);SET dateStr=" SELECT p.catalog_id, p.catalog_title FROM temp_plan_and_stock as p GROUP BY p.catalog_id ORDER BY p.catalog_id ;";
set @sqlselect = dateStr;
prepare stmtselect from @sqlselect;
execute stmtselect;
deallocate prepare stmtselect;
END
BEGINDECLARE catalogID VARCHAR(10000);#100004DECLARE storageID VARCHAR(100);#100000DECLARE startDatre VARCHAR(30);#'2011-09-13 0:00:00' DECLARE endDatre VARCHAR(30);#' '2011-09-18 23:59:59'DECLARE dateStr VARCHAR(10000);#' '2011-09-18 23:59:59'SET catalogID=catalogID_;SET storageID=storageID_;SET startDatre=startDatre_;SET endDatre=endDatre_;CREATE TEMPORARY table IF NOT EXISTS temp_plan_and_stock
select p.catalog_id ,c.title as catalog_title ,p.plan_storage_id, s.title as storage_title, left(p.delivery_date,10) as delivery_date ,
sum(p.quantity) as quantity from plan_storage_sales p,product_catalog c,product_storage_catalog s
where find_in_set (p.catalog_id,product_line_child_list(catalogID) ) and p.catalog_id = c.id and p.plan_storage_id = s.id
and p.plan_storage_id=storageID and p.delivery_date between startDatre and endDatre
group by p.catalog_id, left(p.delivery_date,10);SET dateStr=" SELECT p.catalog_id, p.catalog_title FROM temp_plan_and_stock as p GROUP BY p.catalog_id ORDER BY p.catalog_id ;";
set @sqlselect = dateStr;
prepare stmtselect from @sqlselect;
execute stmtselect;
deallocate prepare stmtselect;
END
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货