在存储过程中最前面加上
SET NOCOUNT ON
----------------------------------------------------------------------------
ALTER Procedure CalculationIncrementRatio_SocialEconomic(@sStartYear as nvarchar(10),@sEndYear as nvarchar(10),@sAreaCode as nvarchar(500),
@sFields as nvarchar(500))
As
Declare @iCurrentFieldPosition as int,
@iCurrentFieldStartPosition as int,
@iCurrentFieldLength as int,
@sSql as nvarchar(1000),
@sCurrentField as nvarchar(50),
@sCurrentAreaCode as nvarchar(50),
@sCurrentAreaName as nvarchar(50),
@iValue as numeric(11,2),
@iOldValue as numeric(11,2),
@iYearInterval as numeric(11,2)
--select 区域名称,区域代码,年份 from 社会经济基本状况调查表 where 区域代码='120224' group by 区域代码,区域名称,年份
ALTER Procedure CalculationIncrementRatio_SocialEconomic(@sStartYear as nvarchar(10),@sEndYear as nvarchar(10),@sAreaCode as nvarchar(500),
@sFields as nvarchar(500))
As
Declare @iCurrentFieldPosition as int,
@iCurrentFieldStartPosition as int,
@iCurrentFieldLength as int,
@sSql as nvarchar(1000),
@sCurrentField as nvarchar(50),
@sCurrentAreaCode as nvarchar(50),
@sCurrentAreaName as nvarchar(50),
@iValue as numeric(11,2),
@iOldValue as numeric(11,2),
@iYearInterval as numeric(11,2)
Set @iYearInterval=Convert(numeric(4,0),@sEndYear)-Convert(numeric(4,0),@sStartYear)
Set @iCurrentFieldPosition=1
Set @iCurrentFieldStartPosition=1
--Set @iCurrentFieldLength=len(@sFields)
Create Table #T2(标识 int identity(1,1) not null,区域名称 nvarchar(50) null,区域代码 nvarchar(50) null,年份 nvarchar(50))
Set @iCurrentFieldPosition=CharIndex(',',@sFields,@iCurrentFieldPosition)
If (@iCurrentFieldPosition=0)
Set @iCurrentFieldLength=Len(@sFields)-@iCurrentFieldStartPosition+1
Else
Set @iCurrentFieldLength=@iCurrentFieldPosition-@iCurrentFieldStartPosition
While @iCurrentFieldPosition<>0
Begin
Set @sCurrentField=Substring(@sFields,@iCurrentFieldStartPosition,@iCurrentFieldLength)
--添加开始年份的统计值
Set @sSql='Insert Into #T2(区域名称,区域代码,年份)
Select 区域名称,区域代码,年份 From 社会经济基本状况调查表 Where 年份=' + @sStartYear + ' and 区域代码 in(' + @sAreaCode +
') Group by 区域代码,区域名称,年份'
Exec(@sSql)
Set @sSql='Insert Into #T1(区域名称,区域代码,年份,指标名称,指标值,递增率,记录分组标记)
Select a.区域名称,a.区域代码,a.年份,''' + @sCurrentField + ''',IsNull(b.[' + @sCurrentField + '],0),0,1 From #T2 a left outer join 社会经济基本状况调查表 b on a.区域名称=b.区域名称 and a.区域代码=b.区域代码 and a.年份=b.年份'
Exec(@sSql)
--return
Delete #T2
--添加结束年份的统计值
Set @sSql='Insert Into #T2(区域名称,区域代码,年份)
Select 区域名称,区域代码,年份 From 社会经济基本状况调查表 Where 年份=' + @sEndYear + ' and 区域代码 in(' + @sAreaCode +
') Group by 区域代码,区域名称,年份'
Exec(@sSql)
Declare IncrementRation_Cursor cursor For Select 区域代码,区域名称 From #T2
--使用游标插入值
OPEN IncrementRation_Cursor
FETCH NEXT FROM IncrementRation_Cursor
INTO @sCurrentAreaCode,@sCurrentAreaName
WHILE @@FETCH_STATUS = 0
BEGIN
--取得开始年份的值
Set @iOldValue=(Select 指标值 From #T1 where 指标名称=@sCurrentField and 区域代码=@sCurrentAreaCode and 年份=@sStartYear)
--插入结束年份的值
Set @sSql='Insert Into #T1(区域名称,区域代码,年份,指标名称,指标值,递增率)
Select ''' + @sCurrentAreaName + ''',''' + @sCurrentAreaCode + ''',''' + @sEndYear + ''',''' + @sCurrentField + ''',' +
'(Select [' + @sCurrentField + '] From 社会经济基本状况调查表 Where 年份=' + @sEndYear + ' and 区域代码=' +
@sCurrentAreaCode +'),1' --@iValue,@iValue/@iOldValue
Exec(@sSql)
--取得刚刚插入的结束年份的值
Set @iValue=(Select 指标值 From #T1 Where 指标名称=@sCurrentField and 区域代码=@sCurrentAreaCode and 年份=@sEndYear and 递增率=1)
--更新递增率
If @iOldValue=0
Update #T1 Set 递增率=0 Where 指标名称=@sCurrentField and 区域代码=@sCurrentAreaCode and 年份=@sEndYear and 递增率=1
Else
Update #T1 Set 递增率=Convert(numeric(5,3),Power(@iValue/@iOldValue,1/@iYearInterval))-1 Where 指标名称=@sCurrentField and 区域代码=@sCurrentAreaCode and 年份=@sEndYear and 递增率=1
FETCH NEXT FROM IncrementRation_Cursor
INTO @sCurrentAreaCode,@sCurrentAreaName
END
CLOSE IncrementRation_Cursor
DEALLOCATE IncrementRation_Cursor
Delete #T2
Set @iCurrentFieldStartPosition=@iCurrentFieldPosition+1
Set @iCurrentFieldPosition=CharIndex(',',@sFields,@iCurrentFieldPosition+1)
If (@iCurrentFieldPosition=0)
Set @iCurrentFieldLength=Len(@sFields)-@iCurrentFieldStartPosition+1
Else
Set @iCurrentFieldLength=@iCurrentFieldPosition-@iCurrentFieldStartPosition
End
/*--计算最后一个统计字段的值
Set @sCurrentField=SubString(@sFields,@iCurrentFieldStartPosition,@iCurrentFieldLength)
--添加开始年份的统计值
Set @sSql='Insert Into #T2(区域名称,区域代码,年份)
Select 区域名称,区域代码,年份 From 社会经济基本状况调查表 Where 年份=' + @sStartYear + ' and 区域代码 in(' + @sAreaCode +
') Group by 区域代码,区域名称,年份'
Exec(@sSql)
Set @sSql='Insert Into #T1(区域名称,区域代码,年份,指标名称,指标值,递增率,记录分组标记)
Select a.区域名称,a.区域代码,a.年份,''' + @sCurrentField + ''',IsNull(b.[' + @sCurrentField + '],0),0,1 From #T2 a left outer join 社会经济基本状况调查表 b on a.区域名称=b.区域名称 and a.区域代码=b.区域代码 and a.年份=b.年份'
Exec(@sSql)
Delete #T2
--添加结束年份的统计值
Set @sSql='Insert Into #T2(区域名称,区域代码,年份)
Select 区域名称,区域代码,年份 From 社会经济基本状况调查表 Where 年份=' + @sEndYear + ' and 区域代码 in(' + @sAreaCode +
') Group by 区域代码,区域名称,年份'
Exec(@sSql)
--使用游标插入值
OPEN IncrementRation_Cursor
FETCH NEXT FROM IncrementRation_Cursor
INTO @sCurrentAreaCode,@sCurrentAreaName
WHILE @@FETCH_STATUS = 0
BEGIN
--取得开始年份的值
Set @iOldValue=(Select 指标值 From #T1 where 指标名称=@sCurrentField and 区域代码=@sCurrentAreaCode and 年份=@sStartYear)
--插入结束年份的值
Set @sSql='Insert Into #T1(区域名称,区域代码,年份,指标名称,指标值,递增率)
Select ''' + @sCurrentAreaName + ''',''' + @sCurrentAreaCode + ''',''' + @sEndYear + ''',''' + @sCurrentField + ''',' +
'(Select [' + @sCurrentField + '] From 社会经济基本状况调查表 Where 年份=' + @sEndYear + ' and 区域代码=' +
@sCurrentAreaCode +'),1' --@iValue,@iValue/@iOldValue
Exec(@sSql)
--取得刚刚插入的结束年份的值
Set @iValue=(Select 指标值 From #T1 Where 指标名称=@sCurrentField and 区域代码=@sCurrentAreaCode and 年份=@sEndYear and 递增率=1)
--更新递增率
If @iOldValue=0
Update #T1 Set 递增率=0 Where 指标名称=@sCurrentField and 区域代码=@sCurrentAreaCode and 年份=@sEndYear and 递增率=1
Else
Update #T1 Set 递增率=Convert(numeric(5,3),Power(@iValue/@iOldValue,1/@iYearInterval))-1 Where 指标名称=@sCurrentField and 区域代码=@sCurrentAreaCode and 年份=@sEndYear and 递增率=1
FETCH NEXT FROM IncrementRation_Cursor
INTO @sCurrentAreaCode,@sCurrentAreaName
END*/
--DEALLOCATE IncrementRation_Cursor
Select 区域名称,区域代码,年份,指标名称,指标值,递增率,记录分组标记 From #T1 Order by 区域代码,区域名称,指标名称,年份
Drop Table #T2
Drop Table #T1
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT ON
GO