日期:2014-05-17 浏览次数:20637 次
---下面的存储是在输入2012,4,2012,12,‘username’ 时就会把2012年4月份的前12个月和2012年12
月份以后的12所月的数据清null, 我要的是这种 如果,但因为这存储是用作报表的数据源的,这样就什么数据都没有了,有什么方法?不能用拼字段实现,因为那样如果我选2012,5,2012,7,那报表就会报缺少字段了,
(要顾及跨年的情况!)
CREATE proc [dbo].[SP_RPTA41]
(@Cid varchar(1) ,
@beginYear int,
@beginMonth int ,
@endYear int,
@endMonth int,
@username nvarchar(50)
)
as
declare @strSql varchar(max)
declare @reduce int
set @reduce=1
while @reduce<@beginMonth
begin
set @strSql = 'update RPT_resultA41 ' +
'set mtd' + cast( @beginYear as nvarchar) + replace(str(@beginMonth-@reduce,2),' ','0') +
' = null where CompanyID = '+LTRIM(@Cid)+' and username ='''+@username+''''
exec (@strSql)
set @reduce = @reduce + 1
end
set @reduce=1
while @reduce<13-@endMonth
begin
set @strSql = 'update RPT_resultA41 ' +
'set mtd' + cast( @endYear as nvarchar) + replace(str(@endMonth+@reduce,2),' ','0') +
' = null where CompanyID = '+LTRIM(@Cid)+' and username ='''+@username+''''
exec (@strSql)
set @reduce = @reduce + 1
end
GO
CREATE proc [dbo].[SP_RPTA41]
(
@Cid varchar(1) ,
@beginYear int,
@beginMonth int ,
@endYear int,
@endMonth int,
@username nvarchar(50)
)
as
declare @dt datetime,@dt2 datetime,@sql varchar(max)
select @dt= RTRIM(@beginYear)+RIGHT(100+@beginMonth,2)+'01',@dt2=RTRIM(@endYear)+RIGHT(100+@endMonth,2)+'01'
set @sql=(select top 12
',mtd'+convert(varchar(6),dateadd(mm,-ROW_NUMBER()over(order by ID),@dt),112)+
'=null,mtd'+convert(varchar(6),dateadd(mm,ROW_NUMBER()over(order by ID),@dt2),112)+'=null'
from syscolumns for xml path(''))
select @sql=STUFF(@sql,1,1,'update RPT_resultA41 set ')+' where CompanyID = '+LTRIM(@Cid)+' and username ='''+@username+''''
select @sql--显示语句
exec(@sql)
GO