检查 Bom 分录物料的有效性，递归 SQL 查询（ 编号：20110630A0957 ）

有如下数据，其中 itemId 相当于 entryItemId 的父 id，

目标：检查指定 id 的所有父节点，是否有它自己本身。

 

 

代码如下：

 

declare @tempTable table( itemId int );declare @ErrCounter int ;declare @ErrCounterMax int ;set @ErrCounter = 0 ;set @ErrCounterMax = 10 ;with a as ( select t1.f_intBomId as bomId , t1.f_intItemId as itemId, t2.f_intItemId as entryItemId from v_bom t1 left join v_bomEntry t2on t1.f_intBomId = t2.f_intBomId )insert @tempTable select itemId from a where entryItemId = 4046while @@rowcount > 0 and @ErrCounter <= @ErrCounterMaxbeginset @ErrCounter = @ErrCounter + 1 ;with b as ( select t1.f_intBomId as bomId , t1.f_intItemId as itemId, t2.f_intItemId as entryItemId from v_bom t1 left join v_bomEntry t2on t1.f_intBomId = t2.f_intBomId)insert @tempTable select itemId from b where entryItemId in ( select * from @tempTable )and itemId not in ( select * from @tempTable )endselect * from @tempTable 

 

存储过程 ：

 

USE [db_NLMRPII]GO/****** 对象: StoredProcedure [dbo].[p_bomEntry_item_validCheck] 脚本日期: 06/30/2011 10:12:20 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[p_bomEntry_item_validCheck]-- Add the parameters for the stored procedure here@itemId intASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;declare @tempTable table( itemId int );declare @ErrCounter int ;declare @ErrCounterMax int ;set @ErrCounter = 0 ;set @ErrCounterMax = 10 ;with a as ( select t1.f_intBomId as bomId , t1.f_intItemId as itemId, t2.f_intItemId as entryItemId from v_bom t1 left join v_bomEntry t2on t1.f_intBomId = t2.f_intBomId )insert @tempTable select itemId from a where entryItemId = @itemIdwhile @@rowcount > 0 and @ErrCounter <= @ErrCounterMaxbeginset @ErrCounter = @ErrCounter + 1 ;with b as ( select t1.f_intBomId as bomId , t1.f_intItemId as itemId, t2.f_intItemId as entryItemId from v_bom t1 left join v_bomEntry t2on t1.f_intBomId = t2.f_intBomId)insert @tempTable select itemId from b where entryItemId in ( select * from @tempTable )and itemId not in ( select * from @tempTable )endselect count(*) as records from @tempTable where itemId = @itemIdEND 

 

最终的版本：

 

USE [db_NLMRPII]GO/****** 对象: StoredProcedure [dbo].[p_bomEntry_item_validCheck] 脚本日期: 06/30/2011 10:37:03 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================-- 使用示例-- DECLARE @return_value int-- -- EXEC @return_value = [dbo].[p_bomEntry_item_validCheck]-- @itemId = 1657-- ,@entryItemId = 29865-- ,@records = 0-- -- if( @return_value > 0) select @return_value-- else select @return_value + 999ALTER PROCEDURE [dbo].[p_bomEntry_item_validCheck]-- Add the parameters for the stored procedure here @itemId int,@entryItemId int,@records int outputASBEGIN-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SET NOCOUNT ON;declare @tempTable table( itemId int );declare @ErrCounter int ;declare @ErrCounterMax int ;set @ErrCounter = 0 ;set @ErrCounterMax = 10 ;with a as ( select t1.f_intBomId as bomId , t1.f_intItemId as itemId, t2.f_intItemId as entryItemId from v_bom t1 left join v_bomEntry t2on t1.f_intBomId = t2.f_intBomId )insert @tempTable select itemId from a where entryItemId = @itemIdwhile @@rowcount > 0 and @ErrCounter <= @ErrCounterMaxbeginset @ErrCounter = @ErrCounter + 1 ;with b as ( select t1.f_intBomId as bomId , t1.f_intItemId as itemId, t2.f_intItemId as entryItemId from v_bom t1 left join v_bomEntry t2on t1.f_intBomId = t2.f_intBomId)insert @tempTable select itemId from b where entryItemId in ( select * from @tempTable )and itemId not in ( select * from @tempTable )endselect @records = count(*) from @tempTable where itemId = @entryItemIdEND 

上面的也不是最终版，下面也有修改：

USE [db_NLMRPII]
GO
/****** 对象:  StoredProcedure [dbo].[p_bomEntry_item_validCheck]    脚本日期: 07/03/2011 10:48:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	检查欲添加的分录物料是否合法，
--                  即，其在自己的 N 级父物料中是否已经存在。
-- =============================================

-- 使用示例

-- DECLARE	@return_value int
-- 			
-- EXEC @return_value = [dbo].[p_bomEntry_item_validCheck]
-- 	 @itemId =  29960
-- 	,@entryItemId = 29960 -- Need to check
-- 			
-- select @return_value


ALTER PROCEDURE [dbo].[p_bomEntry_item_validCheck]
	-- Add the parameters for the stored procedure here
	 @itemId int
	,@entryItemId int -- 需要检查的物料
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;


	declare @tempTable table( itemId int );
	declare @ErrCounter int ;
	declare @ErrCounterMax int ;
	declare @records int ; -- 记录数

	set @ErrCounter = 0 ;
	set @ErrCounterMax = 10  ;
	set @records = 0  ;

	-- 组织关系表
	with a as ( 
		select t1.f_intBomId as bomId 
			, t1.f_intItemId as itemId
			, t2.f_intItemId as entryItemId 
		from v_bom t1 left join v_bomEntry t2
		on t1.f_intBomId = t2.f_intBomId )	
	
	-- 将该BOM的物料ID存入，防止分录物料与BOM物料重复。
	insert @tempTable values( @itemId )

	-- 将该 BOM 物料的所有父物料存入，进入下面循环。
	insert @tempTable select itemId from a where entryItemId = @itemId

	-- 每层循环将临时表中的所有父物料存入（不包含表中已经存在的物料）。
	while @@rowcount > 0 and @ErrCounter <= @ErrCounterMax
	begin

		set @ErrCounter = @ErrCounter + 1 ;
		
		with b as ( 

			select t1.f_intBomId as bomId 
				, t1.f_intItemId as itemId
				, t2.f_intItemId as entryItemId 
			from v_bom t1 left join v_bomEntry t2
			on t1.f_intBomId = t2.f_intBomId
		)	

		insert @tempTable select itemId from b 
			where entryItemId in ( select * from @tempTable )
				and itemId not in ( select * from @tempTable )
	end
		

	-- 查询临时表中是否有欲添加的物料ID，有则无效。
	select @records = count(*) from @tempTable where itemId = @entryItemId
		
	return @records

END