笔记：最近用的一个存储过程

CREATE PROCEDURE CK_MonthKCJS  
(
     @Date_Begin     Datetime,
     @Date_End        Datetime,
     @Date_Temp     Datetime  /*取当前日期的前2个月的日期*/
)
AS

Begin Transaction  /*开始事务*/ 

if not Exists(select * from CK_MonthJS where JSYear=Year(@Date_Begin) and JSMonth=Month(@Date_Begin)) 
begin
    /*期末库存*/
    create table #QMKC 
    (
       JSYear      int,
       JSMonth   int,
       ShuJi_id   varchar(20),
       HuoWei     varchar(20),
       MingCheng  varchar(100),
       ShuJiLeiBie varchar(50),
       CongShu     varchar(50),  
       ZuoZhe     varchar(30),
       ZeBian      varchar(20),
       ChuBanRiQi DateTime,
       DanJia     Float,
       KuCunLiang_QC int,
       KuCunLiang int,
       JinE       Float
    )
    insert into #QMKC 
    select year(@Date_Begin), month(@Date_Begin), A.ShuJi_ID, B.HuoWei, B.MingCheng,B.LeiBie_ID as ShuJiLeiBie, B.CongShu_ID as CongShu, B.ZuoZhe, B.ZeBian,  B.ChuBanRiQi, A.DanJia, C.KuCunLiang_QC as KuCunLiang_QC,   A.KuCunLiang, (A.DanJia* A.KuCunLiang) as JinE
    from CK_KuCunD A left join  JB_ShuJi B on A.ShuJi_ID = B.ID   left join CK_MonthJS_Temp C on C.JSYear=year(@Date_Temp) and C.JSMonth=month(@Date_Temp) and A.ShuJi_id=C.ShuJi_id

    /*insert into #QMKC 
    select year(@Date_Begin), month(@Date_Begin), A.ShuJi_ID, B.HuoWei, B.MingCheng,B.LeiBie_ID as ShuJiLeiBie, B.CongShu_ID as CongShu, B.ZuoZhe, B.ZeBian,  B.ChuBanRiQi, A.DanJia, A.KuCunLiang, (A.DanJia* A.KuCunLiang) as JinE
    from CK_KuCunD A left join  JB_ShuJi B on A.ShuJi_ID = B.ID 
    */
        if (@@error<>0)
    begin
      Rollback Transaction
      return
    End

    /*累计入库*/
    create table #LeiJiRuKu_temp
    (
       ShuJi_id       varchar(20),
       LeiJi_ShuLiang int
    )
    insert into #LeiJiRuKu_temp 
    select ShuJi_ID, sum(ShuLiang) as ShuLiang from CK_RuKuD 
    where ifDaYin = '1'
    group by ShuJi_ID

     if (@@error<>0)
    begin
      Rollback Transaction
      return
    End
    
    /*最初的原始库存量*/
    insert into #LeiJiRuKu_temp 
    select ShuJi_ID,  KuCunLiang_QC  from CK_MonthJS_Temp where JSYear =0 and JSMonth =0

       if (@@error<>0)
    begin
      Rollback Transaction
      return
    End
    /*累计入库*/
    create table #LeiJiRuKu 
    (
       ShuJi_id       varchar(20),
       LeiJi_ShuLiang int
    )
    insert into #LeiJiRuKu 
    select ShuJi_ID, sum(LeiJi_ShuLiang) as LeiJi_ShuLiang from #LeiJiRuKu_temp group by ShuJi_ID     

        if (@@error<>0)
    begin
      Rollback Transaction
      return
    End
    
    /*本月入库*/
    create table #BenYueRuKu 
    (
       ShuJi_id       varchar(20),
       RuKu_ShuLiang  int,
       RuKu_MaYang    float
    )
    
    insert into #BenYueRuKu 
    select ShuJi_ID, sum(ShuLiang) as ShuLiang, sum(MaYang) as MaYang from CK_RuKuD 
    where ifDaYin = '1' and ShiJian Between @Date_Begin and @Date_End
    group by ShuJi_ID
    
        if (@@error<>0)
    begin
      Rollback Transaction
      return
    End

    /*本月退书*/
    create table #BenYueTuiShu 
    (
       ShuJi_id       varchar(20),
       TuiShu_ShuLiang  int,
       TuiShu_MaYang    float,
       TuiShu_ShiYang   float
    )
    
    insert into #BenYueTuiShu
    select ShuJi_ID, sum(ShuLiang_ShiJi) as ShuLiang, sum(ShuLiang_ShiJi*DanJia) as MaYang, Sum(ShuLiang_ShiJi*DanJia*ZheKou/100) as ShiYang from CK_XiaoShouTuiHuoD 
    where ifDaYin = '1'  and ShiJian Between @Date_Begin and @Date_End
    group by ShuJi_ID

        if (@@error<>0)
    begin
      Rollback Transaction
      return
    End
    
    /*本月样书*/
    create table #BenYueYangShu 
    (
       ShuJi_id       varchar(20),
       YangShu_ShuLiang  int,
       YangShu_MaYang    float
    )
    
    insert into #BenYueYangShu
    select ShuJi_ID, sum(ShuLiang) as ShuLiang, sum(MaYang) as MaYang  from XS_YangShuD 
    where ifChuHuo = '1'  and ShiJian Between @Date_Begin and @Date_End
    group by ShuJi_ID

        if (@@error<>0)
    begin
      Rollback Transaction
      return
    End
    
    /*本月销售*/
    create table #BenYueXiaoShou 
    (
       ShuJi_id       varchar(20),
       XiaoShou_ShuLiang  int,
       XiaoShou_MaYang    float,
       XiaoShou_ShiYang   float
    )
    
    insert into #BenYueXiaoShou
    select ShuJi_ID,sum(ShuLiang) as ShuLiang, sum(MaYang) as MaYang, Sum(ShiYang) as ShiYang from XS_XiaoShouD 
    where ifChuHuo = '1'  and ShiJian Between @Date_Begin and @Date_End
    group by ShuJi_ID

        if (@@error<>0)
    begin
      Rollback Transaction
      return
    End
    
    /*将统计数据写入月统计表*/
    insert into CK_MonthJS
    select A.*, B.LeiJi_ShuLiang, C.RuKu_ShuLiang, C.RuKu_MaYang, 
    D.TuiShu_ShuLiang, D.TuiShu_MaYang, D.TuiShu_ShiYang,
    E.YangShu_ShuLiang, E.YangShu_MaYang,
    F.XiaoShou_ShuLiang, F.XiaoShou_MaYang, F.XiaoShou_ShiYang
    from #QMKC A 
    left outer join #LeiJiRuKu B on A.ShuJi_ID=B.ShuJi_ID
    left outer join #BenYueRuKu C on A.ShuJi_ID=C.ShuJi_ID
    left outer join #BenYueTuiShu D on A.ShuJi_ID=D.ShuJi_ID
    left outer join #BenYueYangShu E on A.ShuJi_ID=E.ShuJi_ID
    left outer join #BenYueXiaoShou F on A.ShuJi_ID=F.ShuJi_ID

        if (@@error<>0)
    begin
      Rollback Transaction
      return
    End
    
    /*将统计数据写入月统计表临时表,主要写入期初库存量*/
    insert into CK_MonthJS_Temp
    select  JSYear, JSMonth, ShuJi_id,  MingCheng, KuCunLiang
    from #QMKC 

        if (@@error<>0)
    begin
      Rollback Transaction
      return
    End
    
end


Commit Transaction
GO