动态列!急!

Question

下面的存储是我举例子的,并不正确!我想实现当我输入参数,2012，1，2012，12 的时候,Ytd就
得到那参数区间的和,问题就在,如果是同一年度,那么WHILE @beginm<=@endm就成立,
可如果我输,2011，4，2012，3，那就不成立了,那个字段名也不好拿了，望大家给点办法!谢谢!
下文的
isnull(Mtd201201,0.00) + 用动态@total代替！
写得比较乱,大家看不懂的就在贴子了说明,谢谢!
create proc yyuu
(@beginy int ,
@beginm int ,
@endy int ,
@endm int 
)
as 
declare @total
    WHILE @beginm<=@endm
        BEGIN 
            SET @total=ISNULL(@total+'+','')+'[Mtd'+convert(varchar,@beginy)+RIGHT(100+@beginm,2)+']'
            SET @beginm=@beginm+1
        END 
update dbo.RPT_resultA41_test
set Ytd = 
isnull(Mtd201201,0.00) + 
isnull(Mtd201202,0.00) + 
isnull(Mtd201203,0.00) + 
isnull(Mtd201204,0.00) + 
isnull(Mtd201205,0.00) + 
isnull(Mtd201206,0.00) + 
isnull(Mtd201207,0.00) + 
isnull(Mtd201208,0.00) + 
isnull(Mtd201209,0.00) + 
isnull(Mtd201210,0.00) + 
isnull(Mtd201211,0.00) + 
isnull(Mtd201212,0.00) 
where CompanyID=1 and classify >0 and userName'lop'

Answer 1

declare @beginy int ,
@beginm int ,
@endy int ,
@endm int select @beginy=2012,@beginm=1,@endy=2012,@endm=12 DECLARE @sql NVARCHAR(MAX),@start DATETIME,@end DATETIME
SET @start=RTRIM(@beginy)+'-'+RTRIM(@beginm)+'-01'
SET @end=RTRIM(@endy)+'-'+RTRIM(@endm)+'-01'DECLARE @i INT 
WHILE @start<@end
BEGIN
SET @sql=ISNULL(@sql+'+'+CHAR(10),'')+'isnull([Mtd'+RTRIM(@beginy)+RTRIM(@beginm)+'],0.00)'
SET @start=DATEADD(mm,1,@start)
END
SET @sql='update dbo.RPT_resultA41_test set Ytd ='+@sql +' where CompanyID=1 and classify >0 and userName=''lop'''
PRINT @sql/*
update dbo.RPT_resultA41_test set Ytd =isnull([Mtd20121],0.00)+
isnull([Mtd20121],0.00)+
isnull([Mtd20121],0.00)+
isnull([Mtd20121],0.00)+
isnull([Mtd20121],0.00)+
isnull([Mtd20121],0.00)+
isnull([Mtd20121],0.00)+
isnull([Mtd20121],0.00)+
isnull([Mtd20121],0.00)+
isnull([Mtd20121],0.00)+
isnull([Mtd20121],0.00)where CompanyID=1 and classify >0 and userName='lop'
*/

Answer 2

update dbo.RPT_resultA41_test set Ytd =isnull([Mtd20114],0.00)+
isnull([Mtd20114],0.00)+
isnull([Mtd20114],0.00)+
isnull([Mtd20114],0.00)+
isnull([Mtd20114],0.00)+
isnull([Mtd20114],0.00)+
isnull([Mtd20114],0.00)+
isnull([Mtd20114],0.00)+
isnull([Mtd20114],0.00)+
isnull([Mtd20114],0.00)+
isnull([Mtd20114],0.00) where CompanyID=1 and classify >0 and userName='lop'
不对呢!

Answer 3

declare @beginy int ,
 @beginm int ,
 @endy int ,
 @endm int 
 
select @beginy=2012,@beginm=1,@endy=2012,@endm=12 
 
DECLARE @sql NVARCHAR(MAX),@start DATETIME,@end DATETIME
 SET @start=RTRIM(@beginy)+'-'+RTRIM(@beginm)+'-01'
 SET @end=RTRIM(@endy)+'-'+RTRIM(@endm)+'-01'
 
DECLARE @i INT 
 WHILE @start<@end
 BEGIN
 SET @sql=ISNULL(@sql+'+'+CHAR(10),'')+'isnull([Mtd'+RTRIM(year(@start))+RTRIM(month(@start))+'],0.00)'
 SET @start=DATEADD(mm,1,@start)
 END
 SET @sql='update dbo.RPT_resultA41_test set Ytd ='+@sql +' where CompanyID=1 and classify >0 and userName=''lop'''
 PRINT @sql

Answer 4

declare @beginy int ,
 @beginm int ,
 @endy int ,
 @endm int 
 
select @beginy=2012,@beginm=1,@endy=2012,@endm=12 
 
DECLARE @sql NVARCHAR(MAX),@start DATETIME,@end DATETIME
 SET @start=RTRIM(@beginy)+'-'+RTRIM(@beginm)+'-01'
 SET @end=RTRIM(@endy)+'-'+RTRIM(@endm)+'-01' WHILE @start<=@end
 BEGIN
 SET @sql=ISNULL(@sql+'+'+CHAR(10),'')+'isnull([Mtd'+CONVERT(VARCHAR(6),@start,112)+'],0.00)'
 SET @start=DATEADD(mm,1,@start)
 END
 SET @sql='update dbo.RPT_resultA41_test set Ytd ='+@sql +' where CompanyID=1 and classify >0 and userName=''lop'''
 PRINT @sql
 

Answer 5

为什么多个循环,这里就多个+号的!
update dbo.RPT_resultA41_test
set Ytd =isnull([Mtd201104],0.00)+
isnull([Mtd201105],0.00)+
isnull([Mtd201106],0.00)+
isnull([Mtd201107],0.00)+
isnull([Mtd201108],0.00)+
isnull([Mtd201109],0.00)+
isnull([Mtd201110],0.00)+
isnull([Mtd201111],0.00)+
isnull([Mtd201112],0.00)+
isnull([Mtd201201],0.00)+
isnull([Mtd201202],0.00)+
isnull([Mtd201203],0.00)
where CompanyID=1 and classify >0 and userName='liangzhicheng'
update dbo.RPT_resultA41_test
set Ytd =+
isnull([Mtd201104],0.00)+
isnull([Mtd201105],0.00)+
isnull([Mtd201106],0.00)+
isnull([Mtd201107],0.00)+
isnull([Mtd201108],0.00)+
isnull([Mtd201109],0.00)+
isnull([Mtd201110],0.00)+
isnull([Mtd201111],0.00)+
isnull([Mtd201112],0.00)+
isnull([Mtd201201],0.00)+
isnull([Mtd201202],0.00)+
isnull([Mtd201203],0.00)
where CompanyID=2 and classify >0 and userName='liangzhicheng'