库存台帐报表存储过程

库存台帐报表存储过程，包含While语句，两次循环，原理很简单，但是提供了两次循环的操作方法。

  1 -- ----------------------------------
  2 -- 用途：查询，统计,用于库存台帐
  3 -- ----------------------------------
  4 CREATE   PROCEDURE  dbo.Jmeport_get_storage_price
  5
  6   @maxGoodsCode   varchar ( 10 ),         -- 商品编码大
  7   @minGoodsCode   varchar ( 10 ),         -- 商品编码小
  8   @StartTime   datetime ,             -- 日期大
  9   @EndTime   datetime ,             -- 日期小
 10   @maxStorage   varchar ( 10 ),         -- 仓库编码大
 11   @minStorage   varchar ( 10 )         -- 仓库编码小
 12  
 13 AS
 14      SET  NOCOUNT  ON
 15 declare   @SQL   nvarchar ( 2000 )
 16
 17 CREATE   TABLE  #A(goodsnumber  varchar ( 10 ),goodsname  varchar ( 20 ),goodstype  varchar ( 20 ),dept  varchar ( 10 ),storageid  varchar ( 10 ),storage  varchar ( 36 ),incount  decimal ( 10 , 2 ),
 18 outcount  decimal ( 10 , 2 ),remaincount  decimal ( 10 , 2 ),averageprice  decimal ( 10 , 2 ),countprice  decimal ( 10 , 2 ))
 19
 20 SET   @SQL = '  AND(1=1) '
 21
 22 IF   @maxGoodsCode <> ''
 23 SET   @SQL = @SQL + '  AND( BB05.BB0501<=@maxGoodsCode) '
 24 IF   @minGoodsCode <> ''
 25 SET   @SQL = @SQL + '  AND( BB05.BB0501>=@minGoodsCode) '
 26 IF   @maxStorage <> ''
 27 SET   @SQL = @SQL + '  AND( BA05.BA0501>=@maxStorage) '
 28 IF   @minStorage <> ''
 29 SET   @SQL = @SQL + '  AND( BA05.BA0501<=@minStorage) '
 30
 31
 32 -- 因为没有涉及到出入库内容，所以需要显示全部仓库和商品信息，如果出入库内容不存在，就把那两列设为0
 33 -- SET @SQL=' AND(1=1)'
 34 SET   @SQL = N '
 35 select BB05.BB0501,BB05.BB0503,BB03.BB0303,BB05.BB0506,BA05.BA0501,BA05.BA0502,BA07.BA0703 as 期末结存数量,BA07.BA0704/case when BA07.BA0703=0 then 1 else BA07.BA0703 end as 期末平均成本,BA07.BA0704 as 期末总金额
 36 from BA07 left join BB05 on BA07.BA0702=BB05.BB0501 left join BB03 on BB05.BB0505=BB03.BB0301 left join BA05 on BA07.BA0701=BA05.BA0501
 37 '
 38 + @SQL
 39
 40
 41 INSERT   INTO  #A(goodsnumber,goodsname ,goodstype ,dept ,storageid,storage ,remaincount ,averageprice ,countprice)
 42 EXEC  sp_executesql  @SQL ,
 43 N '
 44  @maxGoodsCode varchar(10),
 45  @minGoodsCode varchar(10),
 46  @StartTime datetime,
 47  @EndTime datetime,
 48  @maxStorage varchar(10),
 49  @minStorage varchar(10)
 50 ' ,
 51   @maxGoodsCode ,         -- 商品编码大
 52   @minGoodsCode ,         -- 商品编码小
 53   @StartTime ,             -- 日期大
 54   @EndTime ,             -- 日期小
 55   @maxStorage ,         -- 仓库编码大
 56   @minStorage      -- 仓库编码小
 57
 58
 59
 60 -- 向临时表中加入incount列的值
 61 declare   @MinGoods   varchar ( 10 ), @MaxGoods   varchar ( 10 ), @MinStorageid   varchar ( 10 ), @MaxStorageid   varchar ( 10 ), @flag   varchar ( 36 ), @incount   decimal ( 10 , 2 )
 62 select   @MinGoods = min (goodsnumber), @MaxGoods = max (goodsnumber)  from  #A
 63 while ( @MinGoods <= @MaxGoods )
 64 Begin
 65
 66      select   @MinStorageid = min (storageid), @MaxStorageid = max (storageid)  from  #A  where  goodsnumber = @MinGoods
 67      while ( @MinStorageid <= @MaxStorageid )
 68      begin
 69          select   @flag = count ( * )  from  #A  where  goodsnumber = @MinGoods   and  storageid = @MinStorageid
 70          if ( @flag <> ' 0 ' )
 71          begin
 72              select   @incount = sum (BA09.BA0904)  from  BA09  left   join  BA10  on  BA09.BA0902 = BA10.BA0902
 73              where  (BA10.BA1002 = ' + ' )  and  (BA09.BB0501 = @MinGoods )  and  (BA10.BA0501 = @MinStorageid )
 74              and  (BA10.AA9902 >= case   when   @StartTime <> ''   then   @StartTime   else   ' 1755-2-6 '   end  )
 75              and  (BA10.AA9902 <= case   when   @EndTime <> ''   then   @EndTime   else   ' 2599-6-7 '   end  )
 76
 77              update  #A  set  incount = @incount   where  goodsnumber = @MinGoods   and  storageid = @MinStorageid
 78          end
 79      select   @MinStorageid = min (Storageid)  from  #A  where  goodsnumber = @MinGoods   and  storageid > @MinStorageid
 80      end
 81 select   @MinGoods = min (goodsnumber)  from  #A  where  goodsnumber > @MinGoods
 82
 83 END
 84 -- 向临时表中加入outcount列的值
 85 select   @MinGoods = min (goodsnumber), @MaxGoods = max (goodsnumber)  from  #A
 86 while ( @MinGoods <= @MaxGoods )
 87 Begin
 88
 89      select   @MinStorageid = min (storageid), @MaxStorageid = max (storageid)  from  #A  where  goodsnumber = @MinGoods
 90      while ( @MinStorageid <= @MaxStorageid )
 91      begin
 92          select   @flag = count ( * )  from  #A  where  goodsnumber = @MinGoods   and  storageid = @MinStorageid
 93          if ( @flag <> ' 0 ' )
 94          begin
 95              select   @incount = sum (BA09.BA0904)  from  BA09  left   join  BA10  on  BA09.BA0902 = BA10.BA0902
 96              where  (BA10.BA1002 = ' - ' )  and  (BA09.BB0501 = @MinGoods )  and  (BA10.BA0501 = @MinStorageid )
 97              and  (BA10.AA9902 >= case   when   @StartTime <> ''   then   @StartTime   else   ' 1755-2-6 '   end  )
 98              and  (BA10.AA9902 <= case   when   @EndTime <> ''   then   @EndTime   else   ' 2599-6-7 '   end  )
 99
100              update  #A  set  outcount = @incount   where  goodsnumber = @MinGoods   and  storageid = @MinStorageid
101          end
102      select   @MinStorageid = min (Storageid)  from  #A  where  goodsnumber = @MinGoods   and  storageid > @MinStorageid
103      end
104 select   @MinGoods = min (goodsnumber)  from  #A  where  goodsnumber > @MinGoods
105
106 END
107
108 -- select  storage,goodsnumber ,goodsname ,sheetdate ,sheetabout ,sheetnumber,outcount ,outprice ,outcountprice ,incount ,inprice ,incountprice from #A
109 -- select * from #A
110 set   @SQL = ' select * from #A '
111 exec ( @SQL )
112
113
114 drop   table  #A
115
116
117 GO
118

转载于:https://www.cnblogs.com/davidathena/archive/2007/06/04/770912.html