日期:2014-05-17 浏览次数:20703 次
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
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)
------解决方案--------------------
提供一个小案例 希望对楼主有帮助。
--测试数据准备 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