写的几个存储过程，复习下了
ALTER proc [dbo].[proc_jiancebiao]@zhiling varchar(1000)AS/***************************名稱: [proc_jiancebiao]作用: 製令多階物料用量表（檢測表）參數：@zhiling,制令號，用","分隔開如:611603084,611000922作者：kk時間：-6-1測試: exec proc_jiancebiao '611603084,611000922'****************************/SET NOCOUNT ONdeclare @t_level table(itemcode varchar(20),child_itemcode varchar(20),level int,qty int)declare @level intdeclare @temp1 table(itemid varchar(20),itemname nvarchar(100),standards nvarchar(100),unit nvarchar(10),qty int)declare @temp2 table(itemid varchar(20),storeQty int)declare @temp3 table(itemid varchar(20),notGetQty int)declare @temp4 table(itemid varchar(20),notReachQty int)set @level=1insert @t_level select ib01,ib03,@level,ib04*num from bomib t1join (select mb04 as itemcode,mb06 as num from mocmb where ','+@zhiling+',' like '%'+mb01+mb02+'%') t2 on t1.ib01=t2.itemcodewhile @@rowcount>0begin set @level=@level+1 insert @t_level select t1.ib01,t1.ib03,@level,t1.ib04*t2.qty from bomib t1 inner join @t_level t2 on t1.ib01=t2.child_itemcode where t2.level=@level-1end--基本資料insert @temp1 select child_itemcode as itemid,t2.ma02 as itemname,t2.ma03 as standards,t2.ma05 as unit,sum(qty) as qty from @t_level t1join invma t2 on t1.child_itemcode = t2.ma01group by child_itemcode,t2.ma02,t2.ma03,t2.ma05--庫存數量insert @temp2 select * from openrowset('MSDASQL','srsdb';'';'','select mb01 as itemid,sum(mb04) as store_num from invmb group by mb01')--下單未領料insert @temp3 select me04 as itemid,sum(me07) as notGetQty from mocmewhere me10='N' and isnull(rtrim(me11),'')<>'' and dbo.changeDXDate(me11)>DATEADD(mm,-4,getdate()) and me01 in ('631','633','651','652')group by me04--在途量insert @temp4 select tb04 as itemid,sum(isnull(tb06,0))-sum(isnull(tb07,0)) as notReachQty from purtbwhere tb14='N' and isnull(rtrim(tb13),'')<>'' and dbo.changeDXDate(tb13)>DATEADD(mm,-4,getdate())group by tb04--查詢結果select '制令別' as itemid,'制令號' as itemname,'品號' as standards,'數量' as unit,qty=null,storeqty=null,notgetqty=null,notreachqty=nullunion all select mb01,mb02,mb04,cast(mb06 as varchar(15)),null,null,null,null from mocmb where ','+@zhiling+',' like '%'+mb01+mb02+'%'union all select '------------','------------','-------------','------------',null,null,null,nullunion allselect a.*,b.storeQty,isnull(c.notGetQty,0)as notGetQty,isnull(d.notReachQty,0) as notReachQtyfrom @temp1 aleft join @temp2 b on a.itemid=b.itemidleft join @temp3 c on a.itemid=c.itemidleft join @temp4 d on a.itemid=d.itemid-------------------------------------ALTER proc [dbo].[p_useThisItemProduct]@itemcode varchar(20)AS/***************************名稱:[p_useThisItemProduct]作用:所有用到此物料的成品及其成本分析參數：@itemcode ,物料品號作者：kk時間：-6-1測試: exec p_useThisItemProduct 'PBXSEBIR6027AW2'****************************/declare @t_level table(itemcode varchar(20),level int)declare @level intset @level=1--查找所有用到此物料的成品及半成品insert @t_level select ib01,@level from bomib where ib03=@itemcodewhile @@rowcount>0begin set @level=@level+1 insert @t_level select t1.ib01,@level from bomib t1 inner join @t_level t2 on t1.ib03=t2.itemcode where t2.level=@level-1end--只保留成品delete from @t_level where left(ltrim(itemcode),1)<>'F'--展開物料清單，計算成本,使用自定義函數dbo.fun_costAnalyse()select t1.itemcode,t2.ma02 as itemname,t2.ma03 as standards,dbo.fun_costAnalyse(t1.itemcode) as cost from @t_level t1left join invma t2 on t1.itemcode = t2.ma01--------------------ALTER proc [dbo].[po_bomZ]@start_date varchar(6),@end_date varchar(6)AS/***************************名稱:po_bomz作用:未出貨訂單多階物料需求用量表參數：@start_date,開始日期，@end_date,結束日期作者：kk時間：-6-1測試: exec po_bomz '060530','060531'****************************/declare @t table(itemcode varchar(20),num int)declare @t_level table(itemcode varchar(20),child_itemcode varchar(20),level int,qty int)declare @level intdeclare @temp1 table(itemid varchar(20),itemname nvarchar(100),standards nvarchar(100),unit nvarchar(10),qty int)declare @temp2 table(itemid varchar(20),storeQty int)declare @temp3 table(itemid varchar(20),notGetQty int)declare @temp4 table(itemid varchar(20),notReachQty int)set @level=1insert @t select tc04 as itemcode,sum(isnull(tc06,0))-sum(isnull(tc07,0)) as num from coptb t1left join coptc t2 on t1.tb01+t1.tb02=t2.tc01+t2.tc02where t1.tb03 >= @start_date and t1.tb03<'1000000' and (t1.tb03<= @end_date or @end_date='') and tc11='N'group by tc04insert @t_level select ib01,ib03,@level,ib04*num from bomib t1inner join @t t2 on t1.ib01=t2.itemcodewhile @@rowcount>0begin set @level=@level+1 insert @t_level select t1.ib01,t1.ib03,@level,t1.ib04*t2.qty from bomib t1 inner join @t_level t2 on t1.ib01=t2.child_itemcode where t2.level=@level-1endinsert @temp1 select child_itemcode as itemid,t2.ma02 as itemname,t2.ma03 as standards,t2.ma05 as unit,sum(qty) as qty from @t_level t1join invma t2 on t1.child_itemcode = t2.ma01group by child_itemcode,t2.ma02,t2.ma03,t2.ma05insert @temp2 select mb01 as itemid,sum(mb04) as store_num from invmb group by mb01insert @temp3 select me04 as itemid,sum(me07) as notGetQty from mocmewhere me10='N' and isnull(rtrim(me11),'')<>'' and dbo.changeDXDate(me11)>DATEADD(mm,-4,getdate()) and me01 in ('631','633','651','652')group by me04insert @temp4 select tb04 as itemid,sum(isnull(tb06,0))-sum(isnull(tb07,0)) as notReachQty from purtbwhere tb14='N' and isnull(rtrim(tb13),'')<>'' and dbo.changeDXDate(tb13)>DATEADD(mm,-4,getdate())group by tb04select a.*,b.storeQty,isnull(c.notGetQty,0)as notGetQty,isnull(d.notReachQty,0) as notReachQtyfrom @temp1 aleft join @temp2 b on a.itemid=b.itemidleft join @temp3 c on a.itemid=c.itemidleft join @temp4 d on a.itemid=d.itemid--未出貨訂單union allselect '----------------','------------------','------------------','-------------------',null,null,null,nullunion allselect '單別','單號','訂單日期','品號',null,null,null,nullunion allselect t1.tb01,t1.tb02,t1.tb03,tc04 as itemcode,sum(isnull(tc06,0))-sum(isnull(tc07,0)) as num,null,null,null from coptb t1left join coptc t2 on t1.tb01+t1.tb02=t2.tc01+t2.tc02where t1.tb03 >= @start_date and t1.tb03<'1000000' and (t1.tb03<= @end_date or @end_date='') and tc11='N'group by t1.tb01,t1.tb02,t1.tb03,t2.tc04