MSSQL 时间通用存储过程

/***********************************************************************************************
author: QLP
last updated: 2005-03-16, awens
remark:t_amount replace with numeric(18,8),double precision replace with numeric(18,8) mssql
***********************************************************************************************/
ALTER  procedure [dbo].[aa_msgoods_startacc] (
  @nyear int,
  @nmonth int
)  as
  declare @goodsid int
  declare @subjectid int
  declare @bmbala  numeric(18,8)
  declare @fbmbala numeric(18,8)
  declare @tmsum   numeric(18,8)
  declare @ftmsum  numeric(18,8)
  declare @bmqtybala  numeric(18,8)
  declare @temp_tab1 table(goodsid integer,
                           subjectid integer,
                           bmbala numeric(18,8),
                           fbmbala numeric(18,8) ,
                           bmqtybala numeric(18,8))
  declare @temp_tab2 table(goodsid integer,
                           subjectid integer,
                           tmsum numeric(18,8),
                           ftmsum numeric(18,8) )


begin
  insert into aa_msgoods (goodsid,subjectid,nyear,nmonth,tmsumdebit,tmsumcredit,tmsum,
                          ftmsumdebit,ftmsumcredit,ftmsum,tmqtysumdebit,tmqtysumcredit)
  select d.goodsid,d.subjectid,@nyear,@nmonth,sum(debit) tmsumdebit,sum(credit) tmsumcredit,
    sum((debit-credit)*dbo.fn_isnotzero(m.origintype-2002)) tmsum,
    sum(rawdebit) ftmsumdebit,sum(rawcredit) ftmsumcredit,
    sum((rawdebit-rawcredit)*dbo.fn_isnotzero(m.origintype-2002)) ftmsum,
    sum(dbo.fn_isnotzero(d.todebit)*quantity) tmqtysumdebit,sum((1-dbo.fn_isnotzero(d.todebit))*quantity) tmqtysumcredit
  from aa_creditem d
  inner join aa_cred m on m.credid=d.credid
  inner join al_subject s on d.subjectid=s.subjectid
  where s.togoods=1 and d.goodsid is not null
        and (d.credid<0 and (d.orderno=2 or d.orderno=3))
  group by d.subjectid,d.goodsid




  insert into @temp_tab1
  select d.goodsid,d.subjectid,sum((s.isdebit*2-1)*(debit+credit)) bmbala,
    sum((s.isdebit*2-1)*(rawdebit+rawcredit)) fbmbala,
    sum((s.isdebit*2-1)*(quantity))  bmqtybala
  from aa_creditem d
  inner join al_subject s on d.subjectid=s.subjectid
  where s.togoods=1 and d.goodsid is not null
    and (d.credid<0 and d.orderno=1)
  group by  d.subjectid,d.goodsid


  declare temp_tab1_cursor cursor for
  select * from @temp_tab1
  open  temp_tab1_cursor
  fetch next from  temp_tab1_cursor
  into  @goodsid,@subjectid,@bmbala,@fbmbala,@bmqtybala
  while @@fetch_status = 0
    begin
      update aa_msgoods set bmbala =@bmbala,fbmbala=@fbmbala,bmqtybala=@bmqtybala
      where goodsid = @goodsid and subjectid = @subjectid
            and nyear=@nyear and nmonth=@nmonth
      fetch next from  temp_tab1_cursor
      into  @goodsid,@subjectid,@bmbala,@fbmbala,@bmqtybala
    end
  close temp_tab1_cursor
  deallocate temp_tab1_cursor --http://www.52mvc.com


  insert into @temp_tab2
  select d.goodsid,d.subjectid,sum((s.isdebit*2-1)*(debit)) tmsum,
    sum((s.isdebit*2-1)*(rawdebit)) ftmsum
  from aa_creditem d
  inner join al_subject s on d.subjectid=s.subjectid
  where s.togoods=1 and d.goodsid is not null
      and (d.credid<0 and d.orderno=4)
  group by  d.subjectid,d.goodsid


  declare temp_tab2_cursor cursor for
  select * from @temp_tab2
  open  temp_tab2_cursor
  fetch next from  temp_tab2_cursor
  into  @goodsid,@subjectid,@tmsum,@ftmsum
  while @@fetch_status = 0
    begin
      update aa_msgoods set tmsum = @tmsum,ftmsum = @ftmsum
      where goodsid = @goodsid and subjectid = @subjectid
           and nyear=@nyear and nmonth=@nmonth
       fetch next from  temp_tab2_cursor
       into  @goodsid,@subjectid,@tmsum,@ftmsum
     end
  close temp_tab2_cursor
  deallocate temp_tab2_cursor
end