110,566
社区成员
发帖
与我相关
我的任务
分享
--创建存储过程
CREATE PROCEDURE dbo.GetAllChildID
@ParentID INT
AS
IF @ParentID IS NULL RETURN
DECLARE @OldRecordCnt INT
DECLARE @CurRecordCnt INT
DECLARE @tmp Table([ID] INT PRIMARY KEY NOT NULL)
INSERT INTO @tmp VALUES(@ParentID)
SET @OldRecordCnt=1
WHILE 1=1
BEGIN
INSERT INTO @tmp SELECT a.[ChildID] FROM TableName a INNER JOIN @tmp b ON a.[ParentID]=b.[ID]
WHERE a.[ChildID] NOT IN (SELECT [ID] FROM @tmp)
SELECT @CurRecordCnt=COUNT(*) FROM @tmp
IF @CurRecordCnt=@OldRecordCnt
BREAK
ELSE
SET @OldRecordCnt=@CurRecordCnt
END
DELETE FROM @tmp WHERE [ID]=@ParentID
SELECT a.* FROM TableName a INNER JOIN @tmp b ON a.ChildID=b.[ID]
GO