if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bomt]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[bomt] GO CREATE TABLE [dbo].[bomt] ( [parent_item] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [itemname] [nvarchar] (100) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [sl] [numeric](19, 6) NULL , [dw] [nvarchar] (8) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [ck] [nvarchar] (8) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [Price] [numeric](19, 6) NULL , [jghb] [nvarchar] (3) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [pl] [smallint] NULL , [ceci] [int] NULL , [fhf] [char] (1) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [scbs] [char] (2) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [path] [nvarchar] (800) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [gw] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [gx] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [cardcode] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [gysn] [nvarchar] (200) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [gc] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [hw] [nvarchar] (20) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [itemwm] [nvarchar] (200) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [tzs] [nvarchar] (100) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [scf] [nvarchar] (50) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [flf] [nvarchar] (10) COLLATE SQL_Latin1_General_CP850_CI_AS NULL , [mjg] [numeric](18, 6) NULL , [mje] [numeric](18, 6) NULL , [je] [numeric](18, 6) NULL , [xjg] [numeric](18, 6) NULL , [rjg] [numeric](18, 6) NULL , [rje] [numeric](18, 6) NULL , [bje] [numeric](18, 6) NULL ) ON [PRIMARY] GO －－－－－－－－－－－存儲過程 CREATE proc BOM @mj nvarchar(20) as begin delete from bomt declare @l int,@bz int set @l=0 INSERT INTO bomt (parent_item, itemname, itemwm,sl, dw, ck, Price, jghb, ceci, scbs, path) select a.itemcode ,a.itemname,a.FrgnName,1,a.InvntryUom,a.DfltWH,a.LstEvlPric,'RMB',@l,a.TreeType,right(space(20)+a.itemcode,20) from oitm a where [email protected] ---- set @bz=(select T1.Qauntity from oitt t1 where [email protected]) while @@rowcount>0 begin set @[email protected]+1 INSERT INTO bomt (parent_item, itemname,itemwm, sl, dw, ck, Price, jghb, ceci, scbs, path,pl,fhf,hw) select i.itemcode,i.itemname,i.FrgnName,a.Quantity, i.InvntryUom,a.Warehouse, a.Price, a.Currency,@l,i.TreeType,path+','+str(a.ChildNum,3)+right(space(20)+i.itemcode,20),a.PriceList,a.IssueMthd,i.sww from (SELECT T1.Father, T1.ChildNum, T1.Code, T1.Quantity/ T0.Qauntity as Quantity , T1.Warehouse, T1.Price, T1.Currency, T1.PriceList, T1.Comment, T1.LogInstanc, T1.Uom,t1.IssueMthd FROM OITT T0 INNER JOIN ITT1 T1 ON T0.Code = T1.Father) a, bomt b,oitt c,oitm i where b.parent_item=c.code and c.code=a.father and a.code= i.itemcode and [email protected] end