恩,这是改过来的 运行到prepare stml from @tsql; 的时候出错 再帮忙看看谢谢。
本帖最后由 ACMAIN_CHM 于 2014-08-04 20:55:50 编辑
CREATE DEFINER=`gerald`@`%` PROCEDURE `sp_get_NoSignBulknew`(IN vSQL varchar(250)) BEGIN declare tId,tOrderId,tXxxs,tXxs,tXs,tS,tM,tL,tXl,tXxl,tXxxl,tXxxxl,tXxxxxl,tF,tQty,tIsCoating,tIsHotPrintLogo int; declare tXxxs1,tXxs1,tXs1,tS1,tM1,tL1,tXl1,tXxl1,tXxxl1,tXxxxl1,tXxxxxl1,tF1,tQty1 int; declare tDes,tPatternNo,tOrderNo,tItemNo,tdeliverydate varchar(150); declare cursql varchar(150); declare done int default 0; declare tsql varchar(400); declare mycur cursor for select * from mycurtable; declare continue handler for not found set done=1; set tsql=concat('create table mycurtable select select p.id,deliverydate,des,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f, productqty,iscoating,ishotprintlogo,patternno,o.id,orderno,itemNo from csp_product as p left join csp_order as o on p.orderid=o.id where',vSQL); prepare stml from @tsql; execute stml;
建议先看看动态sql怎么写法你这里差一句 set @tsql = tsql;
set @tsql=concat('create table mycurtable select select p.id,deliverydate,des,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f, productqty,iscoating,ishotprintlogo,patternno,o.id,orderno,itemNo from csp_product as p left join csp_order as o on p.orderid=o.id where',vSQL);
没这种做法,游标必须是固定的SQL语句你可以先根据传入的参数,组成动态SQL语句执行,这个SQL语句可以创建一个临时表或者视图下一步再定义cursor指向临时表或者视图
哇,有点深奥,有这方面的例子或者blog吗?
SET ·ASQL=CONCAT('CREATE TABLE TT SELECT .... WHERE ',你的条件);
prepare stml from @asql;
execute stml;
游标
SELECT * FROM 此表即可
运行到prepare stml from @tsql; 的时候出错
再帮忙看看谢谢。
BEGIN
declare tId,tOrderId,tXxxs,tXxs,tXs,tS,tM,tL,tXl,tXxl,tXxxl,tXxxxl,tXxxxxl,tF,tQty,tIsCoating,tIsHotPrintLogo int;
declare tXxxs1,tXxs1,tXs1,tS1,tM1,tL1,tXl1,tXxl1,tXxxl1,tXxxxl1,tXxxxxl1,tF1,tQty1 int;
declare tDes,tPatternNo,tOrderNo,tItemNo,tdeliverydate varchar(150);
declare cursql varchar(150);
declare done int default 0;
declare tsql varchar(400);
declare mycur cursor for select * from mycurtable;
declare continue handler for not found set done=1;
set tsql=concat('create table mycurtable select select p.id,deliverydate,des,xxxs,xxs,xs,s,m,l,xl,xxl,xxxl,xxxxl,xxxxxl,f,
productqty,iscoating,ishotprintlogo,patternno,o.id,orderno,itemNo
from csp_product as p left join csp_order as o on p.orderid=o.id
where',vSQL);
prepare stml from @tsql;
execute stml;
productqty,iscoating,ishotprintlogo,patternno,o.id,orderno,itemNo
from csp_product as p left join csp_order as o on p.orderid=o.id
where',vSQL);