1 采用函数
Code
Code
ALTER FUNCTION GetChildID(@ID VARCHAR(36))
RETURNS @Child TABLE(ID VARCHAR(36),Name VARCHAR(50),ParentID VARCHAR(36))
AS
BEGIN
INSERT INTO @Child SELECT ID,Name,ParentID FROM WorkCorps
WHERE ParentID=@ID OR ID=@ID
WHILE @@ROWCOUNT>0
BEGIN
INSERT INTO @Child SELECT A.ID,A.Name,A.ParentID FROM WorkCorps A INNER JOIN @Child B ON A.ParentID=B.ID
WHERE A.ParentID NOT IN (SELECT ParentID FROM @Child)
END
ALTER FUNCTION GetChildID(@ID VARCHAR(36))
RETURNS @Child TABLE(ID VARCHAR(36),Name VARCHAR(50),ParentID VARCHAR(36))
AS
BEGIN
INSERT INTO @Child SELECT ID,Name,ParentID FROM WorkCorps
WHERE ParentID=@ID OR ID=@ID
WHILE @@ROWCOUNT>0
BEGIN
INSERT INTO @Child SELECT A.ID,A.Name,A.ParentID FROM WorkCorps A INNER JOIN @Child B ON A.ParentID=B.ID
WHERE A.ParentID NOT IN (SELECT ParentID FROM @Child)
END
2 采用游标
Code
ALTER FUNCTION GetID(@ID VARCHAR(36))
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @RetValue VARCHAR(5000)
DECLARE @TempID VARCHAR(360)
SET @TempID=''
SET @RetValue=''
--声明两个变量并附空值(赋空值很重要)
DECLARE ID_cursor CURSOR FOR SELECT ID FROM WorkCorps WHERE ParentID=@ID ORDER BY ID DESC
--声明游标 定位到WHERE ParentID=@ID的ID
OPEN ID_cursor
FETCH NEXT FROM ID_cursor INTO @TempID
WHILE(@@FETCH_STATUS=0)
BEGIN
IF LEN(@TempID)>0
SELECT @RetValue=@TempID + ',' + @RetValue
IF((SELECT COUNT(ID) FROM WorkCorps WHERE ParentID=@TempID)>0)
BEGIN
set @RetValue= @RetValue+',' + dbo.GetID(@TempID)
END
--判断子节点是否还有子节点有就递归
FETCH NEXT FROM ID_cursor INTO @TempID
END
CLOSE ID_cursor
DEALLOCATE ID_cursor
SELECT @RetValue=LEFT(@RetValue,LEN(@RetValue)-1)--处理末尾逗号
RETURN @ID + ','+ @RetValue--返回值
ALTER FUNCTION GetID(@ID VARCHAR(36))
RETURNS VARCHAR(500)
AS
BEGIN
DECLARE @RetValue VARCHAR(5000)
DECLARE @TempID VARCHAR(360)
SET @TempID=''
SET @RetValue=''
--声明两个变量并附空值(赋空值很重要)
DECLARE ID_cursor CURSOR FOR SELECT ID FROM WorkCorps WHERE ParentID=@ID ORDER BY ID DESC
--声明游标 定位到WHERE ParentID=@ID的ID
OPEN ID_cursor
FETCH NEXT FROM ID_cursor INTO @TempID
WHILE(@@FETCH_STATUS=0)
BEGIN
IF LEN(@TempID)>0
SELECT @RetValue=@TempID + ',' + @RetValue
IF((SELECT COUNT(ID) FROM WorkCorps WHERE ParentID=@TempID)>0)
BEGIN
set @RetValue= @RetValue+',' + dbo.GetID(@TempID)
END
--判断子节点是否还有子节点有就递归
FETCH NEXT FROM ID_cursor INTO @TempID
END
CLOSE ID_cursor
DEALLOCATE ID_cursor
SELECT @RetValue=LEFT(@RetValue,LEN(@RetValue)-1)--处理末尾逗号
RETURN @ID + ','+ @RetValue--返回值