循环在游标中取数据有关问题

循环在游标中取数据问题

SQL code

alter  procedure Upd_PerMT (@Cid varchar(4), @MtdStartYear int, @beginmonth int) asdeclare @MtdCol nvarchar(20),@strSql nvarchar(1000)declare @strAcctNoBE nvarchar(100)declare @strAcctName nvarchar(100)declare @strCriteriaClassify nvarchar(100)declare @intClassify intset @beginmonth=4while (@beginmonth<=12)beginset @MtdCol = 'mtd'+cast(@MtdStartYear as nvarchar) +  convert(nvarchar,RIGHT(100+@beginmonth,2))DECLARE perMT_Cursor CURSOR FORSELECT AcctNoBE, AcctName, CriteriaClassify, Classify  FROM  RPT_resultA41_perMT   where companyId = @Cid  order by acctNoBeOPEN perMT_CursorFETCH NEXT FROM perMT_Cursor into      @strAcctNoBE, @strAcctName, @strCriteriaClassify, @intClassify        IF (@@FETCH_STATUS <> 0)    BEGIN        BREAK    END      set @strSql =  'update d set d.' +  @MtdCol +'= (   select  SUM(' +  @MtdCol + ') / ' + ' (select a.SalesVolume from f_salesvolumeuser a inner join D_FiscalDate b  on a.FiscalDateID = b.FiscalDateID  and a.companyid = b.companyid   where b.fromdateString =''' + cast(@MtdStartYear as nvarchar) +  convert(nvarchar,RIGHT(100+@beginmonth,2)) + '01'''+ ' and a.CompanyID ='+LTRIM(@Cid)+')from rpt_resultA41 cwhere  c.'+ @strCriteriaClassify +' and c.CompanyID ='+LTRIM(@Cid)+')from rpt_resultA41 d  where d.classify = 0 and d.CompanyID ='+LTRIM(@Cid)+'and d.acctnobe ='''+@strAcctNoBE+''''FETCH NEXT FROM perMT_Cursor into      @strAcctNoBE, @strAcctName, @strCriteriaClassify, @intClassifyprint (@strsql) exec (@strSql)  CLOSE perMT_CursorDEALLOCATE perMT_Cursor    set @beginmonth=@beginmonth+1end

大家看得出

SQL code

from rpt_resultA41 cwhere  c.'+ @strCriteriaClassify +' and c.CompanyID ='+LTRIM(@Cid)+')from rpt_resultA41 d  where d.classify = 0 and d.CompanyID ='+LTRIM(@Cid)+'and d.acctnobe ='''+@strAcctNoBE+''''

中的where c.'+ @strCriteriaClassify +和这个and d.acctnobe ='''+@strAcctNoBE+''''没有改变吗?参数是对的,是循环的问题,大家帮忙看看

[解决办法]
http://www.cnblogs.com/Warmsunshine/archive/2011/02/19/1958449.html
下班了 自己慢慢看
[解决办法]
@@fetch_status=0 试下 去掉break
[解决办法]
给你个游标的实例讲解看看

你这个感觉游标 
IF (@@FETCH_STATUS <> 0)
BEGIN
BREAK
END
这儿有问题
[解决办法]
啊

SQL code

FETCH NEXT FROM perMT_Cursor into      @strAcctNoBE, @strAcctName, @strCriteriaClassify, @intClassifyprint (@strsql) exec (@strSql)
[解决办法]
提供一个小案例 希望对楼主有帮助。
SQL code
--测试数据准备    if(object_id('t1') is not null)drop table t1    CREATE table t1(    id int identity(1,1) primary key,    value nvarchar(20)    )    go    --插入测试数据    insert into t1(value)    select '值1'union all    select '值2'union all    select '值3'union all    select '值4'        --查看结果集合    --select * from t1    if(OBJECT_ID('p_print')is not null) drop procedure p_print    go    create procedure p_print    as    begin        declare @value nvarchar(20)--注意这里的变量类型应该与游标中读取出来的字段类型相同            --创建游标            declare cur1 cursor for        select value from t1        --打开游标        open cur1            fetch next from cur1 into @value--这里的@value对应游标每条记录中的字段value的值             while(@@FETCH_STATUS = 0)            begin                print 'value:'+@value                fetch next from cur1 into @value             end        --关闭游标        close cur1        --释放游标        DEALLOCATE cur1    end        --调用(去注释调用)    --exec p_print        /* 执行结果    value:值1    value:值2    value:值3    value:值4    */ 
  

 

查看更多 下一篇