1：创建BOM展开视图
Create VIEW V_K3_BOM
AS
select distinct
BOM.FID BOMID, BOM.FMATERIALID PFMATERIALID,MATERIAL_L.FNAME PFMATERIALFNAME,MATERIAL.fnumber PFMATERIALFNUMBER,MATERIALBase.FERPCLSID PFERPCLSID,
BOMCHILD.FMATERIALID subFMATERIALID,subMATERIAL.FNUMBER subMATERIALFNUMBER,MATERIAL_L2.FNAME subMATERIALFNAME,subMATERIALBase.FERPCLSID subFERPCLSID,
BOM.FNUMBER BOMFNUMBER,BOM.FUSEORGID FUSEORGID,ORGANIZATIONS.FNUMBER ORGFNUMBER,ORGANIZATIONS_L.FNAME ORGFNAME,BOM.FDOCUMENTSTATUS FDOCUMENTSTATUS
,BOMCHILD.FUQTY,BOM.F_KD_MAINPRODUCTNO,BOM.F_KD_INTEGER
from T_ENG_BOM BOM
inner join T_ENG_BOMCHILD BOMCHILD
on BOMCHILD.FID=BOM.FID
inner join T_BD_MATERIAL MATERIAL
on MATERIAL.FMATERIALID=BOM.FMATERIALID
inner join t_BD_MaterialBase MATERIALBase
on MATERIALBase.FMATERIALID=BOM.FMATERIALID
inner join t_BD_MaterialBase subMATERIALBase
on subMATERIALBase.FMATERIALID=BOMCHILD.FMATERIALID
inner join T_BD_MATERIAL subMATERIAL
on subMATERIAL.FMATERIALID=BOMCHILD.FMATERIALID
inner join T_BD_MATERIAL_L MATERIAL_L
on (MATERIAL_L.FMATERIALID=BOM.FMATERIALID and MATERIAL_L.FLOCALEID='2052')
inner join T_BD_MATERIAL_L MATERIAL_L2
on (MATERIAL_L2.FMATERIALID=BOMCHILD.FMATERIALID and MATERIAL_L2.FLOCALEID='2052')
inner join T_ORG_ORGANIZATIONS_L ORGANIZATIONS_L
on (ORGANIZATIONS_L.FORGID=BOM.FUSEORGID and ORGANIZATIONS_L.FLOCALEID='2052')
inner join T_ORG_ORGANIZATIONS ORGANIZATIONS
on ORGANIZATIONS.FORGID=BOM.FUSEORGID;
2：存储过程传入字符串后需要拆分：例如‘a,b,c’
1)拆分：
函数一
create function SplitIn(@c varchar (2000),@split varchar (2))
returns @t table (col varchar (20))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values ( substring (@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c), '' )
end
insert @t(col) values (@c)
return
end
函数二：
create function [dbo].[f_split](@SourceSql varchar(max),@StrSeprate varchar(10))
returns @temp table(Rowvalue varchar(1000))
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>''
insert @temp values(@SourceSql)
return
end
