sql2000写的一个头痛的存储过程
@nyear integer,
@nmonth integer
)
as
declare @firstday t_date;
declare @lastday t_date;
declare @iemptypeid integer;
declare @idepartmentid integer;
declare @fbaseamt t_amount;
declare @ftax t_amount;
declare @icount integer;
declare @icount2 integer;
declare @ifreeid integer;
declare @idebitsubid integer;
declare @icreditsubid integer;
declare @iddepartmentid integer;
declare @icdepartmentid integer;
declare @idempid integer;
declare @icempid integer;
declare @idprojectid integer;
declare @icprojectid integer;
declare @idgoodsid integer;
declare @icgoodsid integer;
declare @idtraderid integer;
declare @ictraderid integer;
declare @local_tab table(emptypeid integer,
departmentid integer) ;
declare @temp_feebase table(freeid integer not null,
emptypeid integer,
departmentid integer,
debitsubid integer,
creditsubid integer,
ddepartmentid integer,
cdepartmentid integer,
dempid integer,
cempid integer,
dprojectid integer,
cprojectid integer,
dgoodsid integer,
cgoodsid integer,
dtraderid integer,
ctraderid integer)
begin
execute ag_getyearmonthrange @nyear,@nmonth,@firstday output,@lastday output;
insert into @local_tab
select emptypeid,departmentid
from aw_wgcalcfee
where freeid=1
declare aw_fillwgcalcfee_cursor cursor for
select *
from @local_tab
open aw_fillwgcalcfee_cursor
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
while @@fetch_status = 0
begin
select @icount2=count(empid)
from l_employ
where departmentid=@idepartmentid and emptypeid=@iemptypeid
and (closed=0 or (closed=1 and diminishedday>=@firstday and diminishedday<=@lastday))
if (@icount2=0)
begin
delete from aw_wgcalcfee where departmentid=@idepartmentid and emptypeid=@iemptypeid
and nyear=@nyear and nmonth=@nmonth;
end
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
end
close aw_fillwgcalcfee_cursor
-- deallocate aw_fillwgcalcfee_cursor
delete from @local_tab
insert into @temp_feebase
select freeid,emptypeid,departmentid,debitsubid,creditsubid,ddepartmentid,cdepartmentid,
dempid,cempid,dprojectid,cprojectid,dgoodsid,cgoodsid,dtraderid,ctraderid
from aw_wgfeebase
where freeid<4
declare aw_feebase_cursor cursor for
select *
from @temp_feebase
open aw_feebase_cursor
fetch next from aw_feebase_cursor
into @ifreeid,@iemptypeid,@idepartmentid,@idebitsubid,@icreditsubid,@iddepartmentid,@icdepartmentid,
@idempid,@icempid,@idprojectid,@icprojectid,@idgoodsid,@icgoodsid,@idtraderid,@ictraderid
while @@fetch_status = 0
begin
select @icount=count(nyear)
from aw_wgcalcfee
where nyear=@nyear and nmonth=@nmonth and freeid=@ifreeid
and departmentid=@idepartmentid and emptypeid=@iemptypeid
select @icount2=count(empid)
from l_employ
where departmentid=@idepartmentid and emptypeid=@iemptypeid
and (closed=0 or (closed=1 and diminishedday>=@firstday and diminishedday<=@lastday))
if ((@icount=0) and (@icount2>0))
begin
insert into aw_wgcalcfee (nyear,nmonth,freeid,emptypeid,departmentid,debitsubid,
creditsubid,ddepartmentid,cdepartmentid,dempid,cempid,
dprojectid,cprojectid,dgoodsid,cgoodsid,dtraderid,ctraderid)
values (@nyear,@nmonth,@ifreeid,@iemptypeid,@idepartmentid,@idebitsubid,@icreditsubid,
@iddepartmentid,@icdepartmentid,@idempid,@icempid,@idprojectid,
@icprojectid,@idgoodsid,@icgoodsid,@idtraderid,@ictraderid);
end
fetch next from aw_feebase_cursor
into @ifreeid,@iemptypeid,@idepartmentid,@idebitsubid,@icreditsubid,@iddepartmentid,@icdepartmentid,
@idempid,@icempid,@idprojectid,@icprojectid,@idgoodsid,@icgoodsid,@idtraderid,@ictraderid
end
close aw_feebase_cursor
deallocate aw_feebase_cursor
select @ftax=datavalue from am_sysparam where paramid=612;
insert into @local_tab
select emptypeid,departmentid from aw_wgcalcfee
where nyear=@nyear and nmonth=@nmonth and freeid=1
open aw_fillwgcalcfee_cursor
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
while @@fetch_status = 0
begin
select @fbaseamt=sum(feebaseamt) from aw_monthwagefix
where empid in (select empid from l_employ
where emptypeid=@iemptypeid
and departmentid=@idepartmentid)
and nyear=@nyear and nmonth=@nmonth
update aw_wgcalcfee set baseamt=@fbaseamt,tax=@ftax,amount=dbo.fn_round2(@fbaseamt*@ftax/100)
where nyear=@nyear and nmonth=@nmonth and freeid=1
and emptypeid=@iemptypeid and departmentid=@idepartmentid;
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
end
close aw_fillwgcalcfee_cursor
delete from @local_tab
select @ftax=datavalue from am_sysparam where paramid=613;
insert into @local_tab
select emptypeid,departmentid from aw_wgcalcfee
where nyear=@nyear and nmonth=@nmonth and freeid=2
open aw_fillwgcalcfee_cursor
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
while @@fetch_status = 0
begin
select @fbaseamt=sum(feebaseamt) from aw_monthwagefix
where empid in (select empid from l_employ
where emptypeid=@iemptypeid
and departmentid=@idepartmentid)
and nyear=@nyear and nmonth=@nmonth
update aw_wgcalcfee set baseamt=@fbaseamt,tax=@ftax,amount=dbo.fn_round2(@fbaseamt*@ftax/100)
where nyear=@nyear and nmonth=@nmonth and freeid=2
and emptypeid=@iemptypeid and departmentid=@idepartmentid;
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
end
close aw_fillwgcalcfee_cursor
delete from @local_tab
select @ftax=datavalue from am_sysparam where paramid=614;
insert into @local_tab
select emptypeid,departmentid from aw_wgcalcfee
where nyear=@nyear and nmonth=@nmonth and freeid=3
open aw_fillwgcalcfee_cursor
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
while @@fetch_status = 0
begin
select @fbaseamt=sum(feebaseamt) from aw_monthwagefix
where empid in (select empid from l_employ
where emptypeid=@iemptypeid
and departmentid=@idepartmentid)
and nyear=@nyear and nmonth=@nmonth
update aw_wgcalcfee set baseamt=@fbaseamt,tax=@ftax,amount=dbo.fn_round2(@fbaseamt*@ftax/100)
where nyear=@nyear and nmonth=@nmonth and freeid=3
and emptypeid=@iemptypeid and departmentid=@idepartmentid;
fetch next from aw_fillwgcalcfee_cursor
into @iemptypeid,@idepartmentid
end
close aw_fillwgcalcfee_cursor
delete from @local_tab
deallocate aw_fillwgcalcfee_cursor
end
--www.52mvc.com