工作中的递归存储过程

CREATE procedure test107 @mtlno varchar(40),@flag bit=1
as
declare @materialbill varchar(8),--物料单号
	@alternativebom varchar(2),--物料替代号
	@configuration varchar(18), --物料对象号
	@bomcomponent varchar(40),
	@quantity NUMERIC(16,6),
	@classtype varchar(3),
	@isConfFlag varchar(1),
	@count int
--判断是否需要创建临时表
--这个临时表用于存储结果数据
if(@flag=1)
	create table #result (bomcomponent varchar(40),quantity NUMERIC(16,6),classtype varchar(3),isselect varchar(1),isvirtual varchar(1));

--主物料对象号,主物料单号,主物料替代
select top 1 @materialbill=a.materialbill,@alternativebom=a.alternativebom,@configuration=b.configuration
from N_BDMMaterToBOMLink a,(
select top 1 configuralbemtl ,configuration
from  N_MMRPLANTMTL 
where mtlno=@mtlno) b
where a.mtlno=b.configuralbemtl
--select @materialbill ,@alternativebom ,@configuration ;

--判断物料单号是否为空
if(@materialbill is not null)
begin
	--判断物料单号是否被删除
	if (select bomsdeletionindic from  N_bdmbomheader where materialbill=@materialbill and alternativebom=@alternativebom ) ='Y'
	begin
		--删除
		goto labelend;
	end
	else
	begin
		--未被删除
		--创建临时表 用于临时存储数据
		create table #thisbomlist (bomcomponent varchar(40),quantity NUMERIC(16,6),classtype varchar(3),isselect varchar(1),isvirtual varchar(1),speprocuretypefbomitem varchar(2));
		--向临时表插入数据 
		insert into #thisbomlist(bomcomponent,quantity,classtype,speprocuretypefbomitem) 
		(
			select bomcomponent,quantity,classtype,speprocuretypefbomitem
			from N_BDMBOMITEM where bomitemnodeno in 
				(select bomitemnodeno
					from N_BDMBOMSITEMSELECT where materialbill= @materialbill and alternativebom = @alternativebom)
			and materialbill = @materialbill
		)
		--select * from #thisbomlist;
		--选装件处理
		--创建临时表 临时存储特性信息
		create table #tmp (characterval varchar(30),intcharact varchar(10),signnumindic varchar(12));
		insert into #tmp(characterval,intcharact,signnumindic) 
		(
			select 
			charactvalue,intcharact,numberindic
			from 
			N_pcmibsymbol 
			where 
			numberindic in 
			(
				select 
				signnumindic 
				from 
				N_pcmexamchartrestrain 
				where 
				internalCfg = @configuration

			)
		)
		--select * from #tmp;
		--select * from #thisbomlist;
		declare cur cursor for
			select bomcomponent,classtype from #thisbomlist where classType is not null 
		open cur
		fetch next from cur into @bomcomponent,@classtype
		--
		while @@fetch_status = 0
			begin
				--判断该物料是否可以复选
				if((select top 1 multipleObjAllowed from n_pcmclasstypes where classType=@classType) <>'Y')
				begin
					delete  #thisbomlist  WHERE   CURRENT   OF   cur 
					fetch next from cur into @bomcomponent,@classtype
					continue
				end
				select top 1 @bomcomponent = configuration from n_PCMLinkBeInterNumAndObj  where keyobjclassified=@bomcomponent
				--判断该物料是否是下达状态
				if(exists (select top 1 classreleaseindi 
					from n_pcmclassificastatus 
					where classificastatus in (
						select distinct classificastatus 
						from n_pcmallocatableobjtoclass 
						where keyobjclassified=@bomcomponent  and classType = @classtype
						)
					and classType = @classtype and classreleaseindi <> 'Y')
				)
				begin
					delete  #thisbomlist  WHERE   CURRENT   OF   cur 
					fetch next from cur into @bomcomponent,@classtype
					continue
				end
				--判断是否存在与原特性值相匹配的值
				if(exists(select top 1 a.* from 
						(select intcharact,characterval from N_PCMCharacterVal 
						where keyobjclassified =@configuration and internalclassno in 
						(select internalclassno from N_pcmallocatableobjtoclass 
						where keyobjclassified=@configuration  and classType =@classtype)
						) a ,#tmp b 
					where a.intcharact=b.intcharact and a.characterval = b.characterval)
				)
				begin
					fetch next from cur into @bomcomponent,@classtype
					continue
				end

				delete  #thisbomlist  WHERE   CURRENT   OF   cur 

				fetch next from cur into @bomcomponent,@classtype
			end
		close cur
		deallocate cur
		--select * from #thisbomlist;
		--虚拟件处理
		--判断是否是虚拟件
		DECLARE cur2 CURSOR FOR
			select a.bomcomponent,a.quantity,a.classtype from 
			(
				select  bomcomponent,quantity,classtype,speprocuretypefbomitem from #thisbomlist where speprocuretypefbomitem <>'' or speprocuretypefbomitem is not null
				union
				select a.bomcomponent,a.quantity,a.classtype,bb.specprocuretype as speprocuretypefbomitem from #thisbomlist  a 
				join N_MMRPLANTMTL bb on a.bomcomponent=bb.mtlno
				where (bb.specprocuretype <>'' or bb.specprocuretype is not null)
			) a 
			,N_BDMSpecProcureKey b where  b.specprocuretype=a.speprocuretypefbomitem
			and b.phantomitemindic='Y'
		Open cur2
		FETCH NEXT FROM cur2 into @bomcomponent,@quantity,@classtype 
		WHILE @@FETCH_STATUS = 0
		BEGIN
		--select @bomcomponent;

		 if(@classtype is not null)
		 begin
		 	insert into #result (bomcomponent,quantity,classtype,isselect,isvirtual) values (@bomcomponent,@quantity,@classtype,'Y','Y')
			delete from #thisbomlist where bomcomponent = @bomcomponent;
		--虚拟件展开
			exec test107 @bomcomponent, 0
		 end
		 else
		 begin
			insert into #result (bomcomponent,quantity ,classtype,isselect,isvirtual) values (@bomcomponent,@quantity,@classtype,'N','Y')
			delete from #thisbomlist where bomcomponent = @bomcomponent;
		--虚拟件展开
			exec  test107 @bomcomponent, 0
		 end
		 FETCH NEXT FROM cur2 into @bomcomponent,@quantity,@classtype 

		END

		CLOSE cur2
		DEALLOCATE cur2	

		DECLARE cur3 CURSOR FOR
			select #thisbomlist.bomcomponent from #thisbomlist 
		Open cur3
		FETCH NEXT FROM cur3 into @bomcomponent
		WHILE @@FETCH_STATUS = 0
		BEGIN
		set @isConfFlag = (
				select top 1 N_MMRGenMtl.isConfFlag 
					from N_MMRGenMtl where N_MMRGenMtl.mtlno=@bomcomponent
				)
		 if(@isConfFlag ='Y')
			 begin
				set @count = 0
				declare cur4 cursor for
					select configuration from  n_MMRPLANTMTL where configuralbeMtl=@bomcomponent
				open cur4
				fetch next from cur4 into @configuration
				while @@fetch_status = 0
				begin
					if(exists (select signnumindic from #tmp where signnumindic in 
						(select signnumindic  from  n_PCMEXAMCHARTRESTRAIN where internalcfg=@configuration))
					)
					begin
						if(@count = 1)
						begin
							set @count=0
							break
						end
						set @count = 1
						set @bomcomponent = @configuration
					end
				fetch next from cur4 into @configuration
				end
				close cur4
				deallocate cur4
				--set @count = (select count(*) from (
				--	select distinct a.internalcfg from
				--	(
				--	select internalcfg,signnumindic  from  N_PCMEXAMCHARTRESTRAIN 
				--	where internalcfg in (select configuration from  N_MMRPLANTMTL where configuralbeMtl=@bomcomponent) and signnumindic in (select signnumindic from #tmp)
				--	) a 
				--	) b
				--	)
			--select @bomcomponent

				if(@count=1)
				begin
					set @bomcomponent =(select top 1 mtlno from  n_MMRPLANTMTL where configuration=@bomcomponent)
					-- (
					--select top 1 mtlno from N_MMRPLANTMTL where configuration = (select top 1 internalcfg from  N_PCMEXAMCHARTRESTRAIN 
					--		where internalcfg in (select configuration from  N_MMRPLANTMTL where configuralbeMtl=@bomcomponent) and signnumindic in (select signnumindic from #tmp)
					--		 )
					--)
					UPDATE  #thisbomlist   SET   bomcomponent   =  @bomcomponent WHERE   CURRENT   OF  cur3
				end

			 end

		FETCH NEXT FROM cur3 into @bomcomponent
		END

		CLOSE cur3
		DEALLOCATE cur3


		insert into #result(bomcomponent,quantity,classtype,isselect,isvirtual)
		(
			select bomcomponent,quantity,classtype,(case when isselect is not null then 'Y' else 'N' end) isselect,isvirtual
			from #thisbomlist
		)

		--select * from #thisbomlist left join N_MMRGenMtl on N_MMRGenMtl.mtlno = #thisbomlist.bomcomponent
		--select * from #thisbomlist;
		drop table #tmp;
		drop table #thisbomlist;
	end
end

--删除临时表
--select * from #thisbomlist;
--select * from #tmp;


labelend:
--判断是否需要查询和删除临时表
if(@flag=1)
begin
	select * from #result;
	drop table #result;
end
GO

不知道有没有可以优化的空间
先将就用着