zhoubin - 2008-02-25

Hello,everyone!
  I call a procedure by MySQLDriverCS,but it return "{"MySQLDriverCS Exception:
MySQLDriverCS Error: wrong query.PROCEDURE u_smartdialer.DelOverdulData can't return a result set in the given context" }" . c# code and my procedure as below:
c# code:
                    MySQLCommand cmd = new MySQLCommand("call test(?OverdulDays,?cur_EnterpriseId)",(MySQLConnection)base.con);
            cmd.CommandType = CommandType.StoredProcedure;
            MySQLDriverCS.MySQLParameter para1=new MySQLParameter("?OverdulDays",System.Data.DbType.Int32);           
                          para1.Direction = ParameterDirection.Input;
            MySQLDriverCS.MySQLParameter para2=new MySQLParameter("?cur_EnterpriseId",System.Data.DbType.String
            para2.Direction = ParameterDirection.Input;
                    try
                {
                    cmd.Parameters.Clear();
                    para1.Value=3;
                    para2.Value="all";
                    cmd.Parameters.Add(para1);
                    cmd.Parameters.Add(para2);;
                    //cmd.Connection = (MySQLConnection)base.con;
                    cmd.ExecuteNonQuery();

                }
                catch(Exception ex)
                {
                    Console.WriteLine( ex.Message);
                    return false;
                }           
           
                   

DELIMITER $$

DROP PROCEDURE IF EXISTS `u_smartdialer`.`DelOverdulData`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `DelOverdulData`(in OverdulDays  int,in cur_EnterpriseId  varchar(11))
BEGIN
DECLARE done boolean default 0;
DECLARE v_EnterpriseId varchar(11);
DECLARE c_EnterpriseId cursor  for
       select distinct enterpriseid
       from SD_T_ENTERPRISEDESC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
     if cur_EnterpriseId ='all' then
       
    OPEN c_EnterpriseId;
    FETCH c_EnterpriseId into v_EnterpriseId;
    REPEAT
         
        ##Sd_T_A02_EnterpriseId    活动策略信息表   
        set @strTmp= CONCAT('delete from Sd_t_A02_', v_EnterpriseId ,' where campaignstrategyid
        in(select distinct campaignstrategyid from Sd_t_A01_', v_EnterpriseId ,
        ' where campaignid in(
               select distinct Sd_t_A01_', v_EnterpriseId ,'.Campaignid from Sd_t_A01_',v_EnterpriseId ,' where campaignstatus =6 and endtime< (now() -INTERVAL (',OverdulDays,') day))
                  )');
        prepare stmt from @strTmp;
        execute stmt;
        ##Sd_T_A07_EnterpriseId    呼叫列表结果信息   
          set @strTmp= CONCAT('delete from Sd_t_A07_', v_EnterpriseId ,
           ' where campaignid in(
               select distinct Sd_t_A01_', v_EnterpriseId ,'.Campaignid from Sd_t_A01_', v_EnterpriseId ,' where campaignstatus =6 and endtime< (now() -INTERVAL (',OverdulDays,') day)
                  )');
        prepare stmt from @strTmp;
        execute stmt;
        ##Sd_T_R01_EnterpriseId    基础指标量报表
          set @strTmp= CONCAT('delete from Sd_t_r01_', v_EnterpriseId ,
           ' where campaignid in(
               select distinct Sd_t_A01_', v_EnterpriseId ,'.Campaignid from Sd_t_A01_', v_EnterpriseId ,' where campaignstatus =6 and endtime< (now() -INTERVAL (',OverdulDays,') day)
                  )');
        prepare stmt from @strTmp;
        execute stmt;
        ##Sd_T_A01_EnterpriseId    活动基本信息表   
          set @strTmp= CONCAT('delete from Sd_t_a01_', v_EnterpriseId ,
           ' where  campaignstatus =6 and endtime< (now() -INTERVAL (',OverdulDays,') day)');
        prepare stmt from @strTmp;
        execute stmt;
          ##Sd_##Sd_T_B02_EnterpriseId    呼叫列表批次表   
           set @strTmp= CONCAT('delete from Sd_t_b02_', v_EnterpriseId , ' where Sd_t_b02_', v_EnterpriseId , '.isdel =1 or
          (calllistid
              in(select distinct calllistid from Sd_t_b01_', v_EnterpriseId , ' where Sd_t_b01_', v_EnterpriseId , '.isdel =1)
          )');
        prepare stmt from @strTmp;
        execute stmt;
          ##Sd_T_B03_EnterpriseId    呼叫列表详细记录信息   
           set @strTmp= CONCAT('delete from Sd_t_b03_', v_EnterpriseId , ' where Sd_t_b03_', v_EnterpriseId , '.isdel =1 or
          (calllistid
              in(select distinct calllistid from Sd_t_b01_', v_EnterpriseId , ' where Sd_t_b01_', v_EnterpriseId , '.isdel =1)
          )');
        prepare stmt from @strTmp;
        execute stmt;
          ##T_B01_EnterpriseId    呼叫列表描述信息
           set @strTmp= CONCAT('delete from Sd_t_b01_', v_EnterpriseId , ' where Sd_t_b01_', v_EnterpriseId , '.isdel =1
        and Sd_t_b01_', v_EnterpriseId,'.calllistid not in (select distinct calllistid from Sd_t_a01_', v_EnterpriseId,')');
        prepare stmt from @strTmp;
        execute stmt;
         
           commit;
    FETCH c_EnterpriseId into v_EnterpriseId;
    UNTIL done END REPEAT;
       
    CLOSE c_EnterpriseId; /*关闭游标*/   
    SET done=0;

      else
   
    set v_EnterpriseId = cur_EnterpriseId;
       ##Sd_T_A02_EnterpriseId    活动策略信息表   
    set @strTmp= CONCAT('delete from Sd_t_A02_', v_EnterpriseId , ' where campaignstrategyid
    in(select distinct campaignstrategyid from Sd_t_A01_', v_EnterpriseId ,
    ' where campaignid in(
           select distinct Sd_t_A01_', v_EnterpriseId ,'.Campaignid from Sd_t_A01_', v_EnterpriseId ,' where campaignstatus =6 and endtime< (now() -INTERVAL (',OverdulDays,') day))
              )');
    prepare stmt from @strTmp;
    execute stmt;
    ##Sd_T_A07_EnterpriseId    呼叫列表结果信息   
    set @strTmp= CONCAT('delete from Sd_t_A07_', v_EnterpriseId ,
    ' where campaignid in(
           select distinct Sd_t_A01_', v_EnterpriseId ,'.Campaignid from Sd_t_A01_', v_EnterpriseId ,' where campaignstatus =6 and endtime< (now() -INTERVAL (',OverdulDays,') day)
              )');
    prepare stmt from @strTmp;
    execute stmt;
    ##Sd_T_R01_EnterpriseId    基础指标量报表
    set @strTmp= CONCAT('delete from Sd_t_r01_', v_EnterpriseId ,
    ' where campaignid in(
           select distinct Sd_t_A01_', v_EnterpriseId ,'.Campaignid from Sd_t_A01_', v_EnterpriseId ,' where campaignstatus =6 and endtime< (now() -INTERVAL (',OverdulDays,') day)
              )');
    prepare stmt from @strTmp;
    execute stmt;
    ##Sd_T_A01_EnterpriseId    活动基本信息表   
    set @strTmp= CONCAT('delete from Sd_t_a01_', v_EnterpriseId ,
    ' where  campaignstatus =6 and endtime< (now() -INTERVAL (',OverdulDays,') day)');
    prepare stmt from @strTmp;
    execute stmt;
    ##Sd_##Sd_T_B02_EnterpriseId    呼叫列表批次表   
    set @strTmp= CONCAT('delete from Sd_t_b02_', v_EnterpriseId , ' where Sd_t_b02_', v_EnterpriseId , '.isdel =1 or
    (calllistid
          in(select distinct calllistid from Sd_t_b01_', v_EnterpriseId , ' where Sd_t_b01_', v_EnterpriseId , '.isdel =1)
    )');
    prepare stmt from @strTmp;
    execute stmt;
    ##Sd_T_B03_EnterpriseId    呼叫列表详细记录信息   
    set @strTmp= CONCAT('delete from Sd_t_b03_', v_EnterpriseId , ' where Sd_t_b03_', v_EnterpriseId , '.isdel =1 or
    (calllistid
          in(select distinct calllistid from Sd_t_b01_', v_EnterpriseId , ' where Sd_t_b01_', v_EnterpriseId , '.isdel =1)
    )');
    prepare stmt from @strTmp;
    execute stmt;
    ##T_B01_EnterpriseId    呼叫列表描述信息
    set @strTmp= CONCAT('delete from Sd_t_b01_', v_EnterpriseId , ' where Sd_t_b01_', v_EnterpriseId , '.isdel =1
        and Sd_t_b01_', v_EnterpriseId,'.calllistid not in (select distinct calllistid from Sd_t_a01_', v_EnterpriseId,')');
    prepare stmt from @strTmp;
    execute stmt;
    commit;
      end if;
END$$

DELIMITER ;