日期:2014-05-17  浏览次数:20703 次

循环在游标中取数据问题
SQL code

alter  procedure Upd_PerMT
 (@Cid varchar(4),
 @MtdStartYear int,
 @beginmonth int) 
as
declare @MtdCol nvarchar(20),@strSql nvarchar(1000)
declare @strAcctNoBE nvarchar(100)
declare @strAcctName nvarchar(100)
declare @strCriteriaClassify nvarchar(100)
declare @intClassify int
set @beginmonth=4

while (@beginmonth<=12)
begin
set @MtdCol = 'mtd'+cast(@MtdStartYear as nvarchar) +  convert(nvarchar,RIGHT(100+@beginmonth,2))
DECLARE perMT_Cursor CURSOR FOR
SELECT AcctNoBE, AcctName, CriteriaClassify, Classify
  FROM  RPT_resultA41_perMT 
  where companyId = @Cid
  order by acctNoBe
OPEN perMT_Cursor
FETCH 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 c
where  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, @intClassify
print (@strsql)
 exec (@strSql)
  CLOSE perMT_Cursor
DEALLOCATE perMT_Cursor 
   set @beginmonth=@beginmonth+1
end




大家看得出
SQL code
from rpt_resultA41 c
where  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, @intClassify
print (@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