mysql动态sql应用
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_insertProduct`(in p_quoted_id integer,in p_corderId integer,in p_productIds varchar(60))
begin
declare v_sql varchar(2000);
declare v_sql2 varchar(2000);
set v_sql= concat('insert into tbl_crm_orderproduct(
  crm_orderp_product_sid,
  crm_orderp_corder_sid,
  Crm_orderp_quoted_sid,
  Crm_orderp_size_len,
  Crm_orderp_cost,
  Crm_orderp_labor_costs,
  Crm_orderp_type,
  Crm_orderp_mask,
  Crm_orderp_cadjust,
  Crm_orderp_badjust,
  Crm_orderp_madjust,
  Crm_orderp_sadjust,
  Crm_orderp_padjust,
  Crm_orderp_amount,
  Crm_orderp_main_waste,
  Crm_orderp_points,
  Crm_orderp_client_code)
        SELECT
          crm_orderp_product_sid,
          ',p_corderId,',
          ',p_quoted_id,',
  Crm_orderp_size_len,
  Crm_orderp_cost,
  Crm_orderp_labor_costs,
  Crm_orderp_type,
  Crm_orderp_mask,
  Crm_orderp_cadjust,
  Crm_orderp_badjust,
  Crm_orderp_madjust,
  Crm_orderp_sadjust,
  Crm_orderp_padjust,
  Crm_orderp_amount,
  Crm_orderp_main_waste,
  Crm_orderp_points,
  Crm_orderp_client_code
        FROM  tbl_crm_orderproduct cb
        where crm_orderp_corder_sid = (select Crm_corder_from_sid from tbl_crm_customerorder where Crm_corder_sid=' ,p_corderId,')
                  and  crm_orderp_product_sid in (',p_productIds,')
                  and not exists(select 1 from tbl_crm_orderproduct op where cb.crm_orderp_product_sid = op.crm_orderp_product_sid   and crm_orderp_corder_sid =  ',p_corderId,')' );

select v_sql;

         set @frist_sql=v_sql;
         prepare stmt from @frist_sql;
         EXECUTE stmt;

  set v_sql2= concat('insert into tbl_crm_orderproduct(crm_orderp_product_sid,crm_orderp_corder_sid,Crm_orderp_quoted_sid,Crm_orderp_size_len)
        select
     crm_orderp_product_sid,
     ',p_corderId,',
        ',p_quoted_id,',
     Crm_orderp_size_len
from tbl_complex_baseproductinfo cb
         where not exists(select 1 from tbl_crm_orderproduct op where cb.crm_orderp_product_sid = op.crm_orderp_product_sid and crm_orderp_corder_sid = ', p_corderId,')
   and  product_id in (',p_productIds,')');

select v_sql2;

set @second_sql=v_sql2;
         prepare stmt from @second_sql;
         EXECUTE stmt;

         deallocate prepare stmt;

end
lunzi   2010-07-28 23:37:39 评论:0   阅读:2041   引用:0

发表评论>>

署名发表(评论可管理,不必输入下面的姓名)

姓名:

主题:

内容: 最少15个,最长1000个字符

验证码: (如不清楚,请刷新)

Copyright@2004-2010 powered by YuLog