截止目前为止写过的一个最长的SQL存储过程

------------------------------------
--用途：根据各表的数据初始化月报报表的数据
--项目名称：月报数据初始化
--说明：从月报初始化表中获取最大的年份与月份，与当前时间的月份进行大小比较，自动添加当前月份及之前的所有记录。
--创建人：戴明军
--时间：2010-5-23 17:48:00
------------------------------------
CREATE PROCEDURE UP_PM_MonthReport_Initial

AS
------------------------原煤产量------------------
declare @Yearcurrent int
declare @Monthcurrent int
declare @YearMax int
declare @MonthMax int
declare @diffyear int
declare @diffmonth int
--declare @classtype nvarchar(50)
set @Monthcurrent=Month(getdate())
set @Yearcurrent=Year(getdate())
set @YearMax=(select Max(MRYear) from PM_MonthReport where MRDepType='采煤' )

if(@YearMax is null)
begin
set @YearMax=1990
end

set @MonthMax=(select Max(MRMonth) from  PM_MonthReport where MRYear=@Yearcurrent and MRDepType='采煤')

if(@MonthMax is null)
begin
set @MonthMax=1
end

set @diffyear=@Yearcurrent-@YearMax
set @diffmonth=@Monthcurrent-@MonthMax

if(@diffyear>=0 and @diffmonth>0)
begin
declare @time datetime,@id int,@num decimal,@year int,@month int
DECLARE tmp_cursor CURSOR FOR
select MineMonthPlanTime, MineMonthPlanDepartmentID , sum(MineMonthPlanMonthPlan) from PM_MineMonthPlan
 where  @YearMax<=year(MineMonthPlanTime) and year(MineMonthPlanTime)<=@Yearcurrent and  @MonthMax<month(MineMonthPlanTime) and month(MineMonthPlanTime)<=@Monthcurrent and delflag=0
GROUP BY MineMonthPlanTime, MineMonthPlanDepartmentID
OPEN tmp_cursor
FETCH NEXT FROM tmp_cursor
INTO @time,@id,@num

while @@FETCH_STATUS = 0
begin
set @year=year(@time)
set @month=month(@time)
insert into PM_MonthReport ( MRYear,MRMonth,MRDepKeycode,MRDepType,MRJihua) values (@year,@month,@id,'采煤',@num)
FETCH NEXT FROM tmp_cursor
INTO @time,@id,@num
end
CLOSE tmp_cursor
DEALLOCATE tmp_cursor
end

--------------------------------------------开掘进尺--------------------------

else

declare @DYearMax int
declare @DMonthMax int
declare @Ddiffyear int
declare @Ddiffmonth int
set @DYearMax=(select Max(MRYear) from PM_MonthReport where MRDepType='掘进' or MRDepType='开拓' )

if(@DYearMax is null)
begin
set @DYearMax=1990
end

set @DMonthMax=(select Max(MRMonth) from  PM_MonthReport where MRYear=@Yearcurrent and MRDepType='掘进' or MRDepType='开拓'  )

if(@DMonthMax is null)
begin
set @DMonthMax=1
end

set @Ddiffyear=@Yearcurrent-@DYearMax
set @Ddiffmonth=@Monthcurrent-@DMonthMax
if(@Ddiffyear>=0 and @Ddiffmonth>0)
begin
declare @dtime datetime,@did int,@dnum decimal,@dyear int,@dmonth int,@hid int,@deptype nvarchar(50)
DECLARE tmp_cursor_2 CURSOR FOR
select DriveMonthPlanTime, DriveMonthPlanDepartmentID , DriveMonthPlanMonthPlan,DriveMonthPlanHangdaoID from PM_DriveMonthPlan
 where  @DYearMax<=year(DriveMonthPlanTime) and year(DriveMonthPlanTime)<=@Yearcurrent and  @DMonthMax<month(DriveMonthPlanTime) and month(DriveMonthPlanTime)<=@Monthcurrent and delflag=0
OPEN tmp_cursor_2
FETCH NEXT FROM tmp_cursor_2
INTO @dtime,@did,@dnum,@hid

while @@FETCH_STATUS = 0
begin
set @dyear=year(@dtime)
set @dmonth=month(@dtime)
set @deptype=(select DepType from PM_Department where  DepID=@did  and delflag=0 )

insert into PM_MonthReport ( MRYear,MRMonth,MRDepKeycode,MRDepType,MRJihua,MRHangdaoKeycode) values (@dyear,@dmonth,@did,@deptype,@dnum,@hid)
FETCH NEXT FROM tmp_cursor_2
INTO @dtime,@did,@dnum,@hid
end
CLOSE tmp_cursor_2
DEALLOCATE tmp_cursor_2
end
----------------------------------全矿、掘进煤、总进尺------------------------------
declare @ZYearMax int
declare @ZMonthMax int
declare @Zdiffyear int
declare @Zdiffmonth int
set @DYearMax=(select Max(MRYear) from PM_MonthReport where MRDepKeycode<0 )

if(@ZYearMax is null)
begin
set @ZYearMax=1990
end

set @ZMonthMax=(select Max(MRMonth) from  PM_MonthReport where MRYear=@Yearcurrent and MRDepKeycode<0)

if(@ZMonthMax is null)
begin
set @ZMonthMax=1
end

set @Zdiffyear=@Yearcurrent-@ZYearMax
set @Zdiffmonth=@Monthcurrent-@ZMonthMax

if(@Zdiffyear>=0 and @Zdiffmonth>0)
begin
declare @Zyear  nvarchar(50),@Zmonth nvarchar(50)
declare @Zqknum decimal,@Zjm decimal,@Zjc decimal

DECLARE tmp_cursor_3 CURSOR FOR
select [Year], [Month] , MonthPlanJihuachanliang,MonthPlanJuejinchanliang,MonthPlanZongjinchi from PM_MonthPlan
 where  @ZYearMax<=convert(int,[Year]) and convert(int,[Year])<=@Yearcurrent and  @ZMonthMax<convert(int,[Month]) and convert(int,[Month])<=@Monthcurrent
OPEN tmp_cursor_3
FETCH NEXT FROM tmp_cursor_3
INTO @Zyear,@Zmonth,@Zqknum,@Zjm,@Zjc

while @@FETCH_STATUS = 0
begin

insert into PM_MonthReport ( MRYear,MRMonth,MRDepKeycode,MRDepType,MRJihua) values (@Zyear,@Zmonth,-1,'采煤',@Zqknum)
insert into PM_MonthReport ( MRYear,MRMonth,MRDepKeycode,MRDepType,MRJihua) values (@Zyear,@Zmonth,-2,'采煤',@Zjm)
insert into PM_MonthReport ( MRYear,MRMonth,MRDepKeycode,MRDepType,MRJihua) values (@Zyear,@Zmonth,-3,'掘进',@Zjc)

FETCH NEXT FROM tmp_cursor_3
INTO @Zyear,@Zmonth,@Zqknum,@Zjm,@Zjc
end
CLOSE tmp_cursor_3
DEALLOCATE tmp_cursor_3
end

GO