查询树节点以及所有子节点

SQLServer查询树节点以及所有的子节点
存储树节点表结构：

SQLServer查询树节点以及所有的子节点主要有两种方式：
第一种：通过标量值函数查询：
   USE [YXCSC]
GO
/****** Object:  UserDefinedFunction [dbo].[GetInfoCustomChildCode]    Script Date: 02/20/2014 15:59:49 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:	<Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[GetInfoCustomChildCode]
(
	@CUSGRO_ID varchar(50) 
)
RETURNS nvarchar(4000)
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Rtn nvarchar(4000),@Tmp varchar(50) ,@ChildCou int ;
    declare tb cursor for Select CUSGRO_ID from CC_CUSGROUP where CUSGRO_PARENTID=@CUSGRO_ID;
    open tb;
    set @Rtn='';
    fetch next from tb into @Tmp
    while @@fetch_status=0
    begin
		if @Rtn<>''
			set @Rtn=@Rtn+',';
		set @Rtn=@Rtn+convert(varchar(50),@Tmp);

		Select @ChildCou=count(*) from CC_CUSGROUP where  CUSGRO_PARENTID=convert(varchar(50),@Tmp);
		--print @ChildCou
		if @ChildCou>0
		begin
			set @Rtn=@Rtn+','+dbo.GetInfoCustomChildCode(@Tmp);
			--return @Rtn
		end

		fetch next from tb into @Tmp	
    end
    close tb
	-- Return the result of the function
	if @Rtn<>''
	   -- set @Rtn=@CUSGRO_ID+','+@Rtn
		set @Rtn=Convert(varchar(50),@CUSGRO_ID)+','+@Rtn
	else
		--set @Rtn=@CUSGRO_ID
		set @Rtn=Convert(varchar(50),@CUSGRO_ID)
	RETURN @Rtn
END
测试查询：
SELECT  dbo.GetInfoCustomChildCode(100000);其中100000为传入的树节点。
此方法返回结果为：@Rtn=10000,100001,100002,100003...形式，如果树节点深度增加，则返回结果超过nvarchar(4000)的最大值，导致数据不准确。对于数据比较多的树，此方法不可取。

第一种：通过表值函数查询：
USE [YXCSC]
GO
/****** Object:  UserDefinedFunction [dbo].[GetCustomChildCode]    Script Date: 02/20/2014 16:04:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER FUNCTION [dbo].[GetCustomChildCode]
(	
	@CUSGRO_PARENTID varchar(50) 
)
RETURNS 
   @tree TABLE 
(

	CUSGRO_PARENTID varchar(20),
	CUSGRO_ID varchar(20),
	CUSGRO_NAME varchar(50),
	lev        char(2)
)
AS
BEGIN
 DECLARE @lev INT;
  SET @lev = 1;
   INSERT INTO @tree values(@CUSGRO_PARENTID,@CUSGRO_PARENTID,'11','0');
   INSERT INTO @tree
    SELECT
      CUSGRO_ID, CUSGRO_PARENTID, CUSGRO_NAME, 1
    FROM
      CC_CUSGROUP
    WHERE
      CC_CUSGROUP.CUSGRO_PARENTID = @CUSGRO_PARENTID and CUSGRO_FLAG='0';

 -- 循环插入.
  WHILE @@ROWCOUNT > 0
  BEGIN
    -- 递增Lev
    SET @lev = @lev + 1;
    -- 假如存在数据异常，造成过多的循环。
    IF @lev > 55
      BREAK;
    -- 插入数据.
    INSERT INTO @tree
      SELECT
        CC_CUSGROUP.CUSGRO_ID, CC_CUSGROUP.CUSGRO_PARENTID, CC_CUSGROUP.CUSGRO_NAME, @lev
      FROM
        CC_CUSGROUP
          JOIN @tree t
            ON ( CC_CUSGROUP.CUSGRO_PARENTID = t.CUSGRO_PARENTID
                AND t.lev = @lev - 1)
        where CUSGRO_FLAG='0';
  END;
  RETURN;
END
此方法可以解决树节点数据比较多得情况。