库存台帐报表存储过程,包含While语句,两次循环,原理很简单,但是提供了两次循环的操作方法。
1------------------------------------
2--用途:查询,统计,用于库存台帐
3------------------------------------
4CREATE 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
13AS
14 SET NOCOUNT ON
15declare @SQL nvarchar(2000)
16
17CREATE TABLE #A(goodsnumber varchar(10),goodsname varchar(20),goodstype varchar(20),dept varchar(10),storageid varchar(10),storage varchar(36),incount decimal(10,2),
18outcount decimal(10,2),remaincount decimal(10,2),averageprice decimal(10,2),countprice decimal(10,2))
19
20SET @SQL=' AND(1=1)'
21
22IF @maxGoodsCode<>''
23SET @SQL=@SQL+' AND( BB05.BB0501<=@maxGoodsCode)'
24IF @minGoodsCode<>''
25SET @SQL=@SQL+' AND( BB05.BB0501>=@minGoodsCode)'
26IF @maxStorage<>''
27SET @SQL=@SQL+' AND( BA05.BA0501>=@maxStorage)'
28IF @minStorage<>''
29SET @SQL=@SQL+' AND( BA05.BA0501<=@minStorage)'
30
31
32--因为没有涉及到出入库内容,所以需要显示全部仓库和商品信息,如果出入库内容不存在,就把那两列设为0
33--SET @SQL=' AND(1=1)'
34SET @SQL=N'
35select 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 期末总金额
36from 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
41INSERT INTO #A(goodsnumber,goodsname ,goodstype ,dept ,storageid,storage ,remaincount ,averageprice ,countprice)
42EXEC sp_executesql @SQL,
43N'
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列的值
61declare @MinGoods varchar(10),@MaxGoods varchar(10),@MinStorageid varchar(10),@MaxStorageid varchar(10),@flag varchar(36),@incount decimal(10,2)
62select @MinGoods=min(goodsnumber),@MaxGoods=max(goodsnumber) from #A
63while(@MinGoods<=@MaxGoods)
64Begin
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
81select @MinGoods=min(goodsnumber) from #A where goodsnumber>@MinGoods
82
83END
84--向临时表中加入outcount列的值
85select @MinGoods=min(goodsnumber),@MaxGoods=max(goodsnumber) from #A
86while(@MinGoods<=@MaxGoods)
87Begin
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
104select @MinGoods=min(goodsnumber) from #A where goodsnumber>@MinGoods
105
106END
107
108--select storage,goodsnumber ,goodsname ,sheetdate ,sheetabout ,sheetnumber,outcount ,outprice ,outcountprice ,incount ,inprice ,incountprice from #A
109--select * from #A
110set @SQL='select * from #A'
111exec(@SQL)
112
113
114drop table #A
115
116
117GO
118
2--用途:查询,统计,用于库存台帐
3------------------------------------
4CREATE 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
13AS
14 SET NOCOUNT ON
15declare @SQL nvarchar(2000)
16
17CREATE TABLE #A(goodsnumber varchar(10),goodsname varchar(20),goodstype varchar(20),dept varchar(10),storageid varchar(10),storage varchar(36),incount decimal(10,2),
18outcount decimal(10,2),remaincount decimal(10,2),averageprice decimal(10,2),countprice decimal(10,2))
19
20SET @SQL=' AND(1=1)'
21
22IF @maxGoodsCode<>''
23SET @SQL=@SQL+' AND( BB05.BB0501<=@maxGoodsCode)'
24IF @minGoodsCode<>''
25SET @SQL=@SQL+' AND( BB05.BB0501>=@minGoodsCode)'
26IF @maxStorage<>''
27SET @SQL=@SQL+' AND( BA05.BA0501>=@maxStorage)'
28IF @minStorage<>''
29SET @SQL=@SQL+' AND( BA05.BA0501<=@minStorage)'
30
31
32--因为没有涉及到出入库内容,所以需要显示全部仓库和商品信息,如果出入库内容不存在,就把那两列设为0
33--SET @SQL=' AND(1=1)'
34SET @SQL=N'
35select 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 期末总金额
36from 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
41INSERT INTO #A(goodsnumber,goodsname ,goodstype ,dept ,storageid,storage ,remaincount ,averageprice ,countprice)
42EXEC sp_executesql @SQL,
43N'
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列的值
61declare @MinGoods varchar(10),@MaxGoods varchar(10),@MinStorageid varchar(10),@MaxStorageid varchar(10),@flag varchar(36),@incount decimal(10,2)
62select @MinGoods=min(goodsnumber),@MaxGoods=max(goodsnumber) from #A
63while(@MinGoods<=@MaxGoods)
64Begin
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
81select @MinGoods=min(goodsnumber) from #A where goodsnumber>@MinGoods
82
83END
84--向临时表中加入outcount列的值
85select @MinGoods=min(goodsnumber),@MaxGoods=max(goodsnumber) from #A
86while(@MinGoods<=@MaxGoods)
87Begin
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
104select @MinGoods=min(goodsnumber) from #A where goodsnumber>@MinGoods
105
106END
107
108--select storage,goodsnumber ,goodsname ,sheetdate ,sheetabout ,sheetnumber,outcount ,outprice ,outcountprice ,incount ,inprice ,incountprice from #A
109--select * from #A
110set @SQL='select * from #A'
111exec(@SQL)
112
113
114drop table #A
115
116
117GO
118
==========================签名==========================
大丈夫上马横刀平天下,下马回家养妻小,做一番一生引以为豪的事业,找一个一生荣辱与共的妻子,在有生之年报答帮过我的人,并有能力帮助需要帮助的人。
大丈夫上马横刀平天下,下马回家养妻小,做一番一生引以为豪的事业,找一个一生荣辱与共的妻子,在有生之年报答帮过我的人,并有能力帮助需要帮助的人。