sql 递归函数，根据父级ID获取所有子级(含自己)

 

------------------------------------
--用途：根据商家ID获取所有子商家(含自己商家) 
--说明：
--时间：2011/5/6 12:44:42
--作者：李海生@Bool为1时包括自己商家信息0时不包括
--例子：select * from GetChildParent(17,1)
------------------------------------

ALTER FUNCTION [dbo].[GetChildParent]
    (
      @ID VARCHAR(10) ,
      @bool INT
    )
RETURNS @t TABLE
    (
      ID VARCHAR(10) ,
      ParentID VARCHAR(10) ,
      OrgName NVARCHAR(50) ,
      Level INT
    )
AS
    BEGIN 
        DECLARE @counts INT
        SELECT  @counts = ISNULL(COUNT(*), 0)
        FROM    dbo.Customers_Business
        WHERE   Bid = @ID
        IF ( @counts > 0 )
            BEGIN
   
                DECLARE @OrgName NVARCHAR(1000)
                SELECT  @OrgName = CONVERT(NVARCHAR(1000), Name) + '('
                        + CONVERT(NVARCHAR(1000), Bid) + ')'
                FROM    Customers_Business
                WHERE   Bid = @ID 
                DECLARE @i INT 
                SET @i = 1 
                IF ( @bool > 0 )
                    BEGIN
                        INSERT  INTO @t
                                SELECT  @ID ,
                                        @ID ,
                                        @OrgName ,
                                        0 --当前级，本级，如果不要的话可以注释掉或再加个参数来选择操作 
                        INSERT  INTO @t
                                SELECT  Bid ,
                                        ParentId ,
                                        CONVERT(NVARCHAR(1000), Name) + '('
                                        + CONVERT(NVARCHAR(1000), Bid) + ')' ,
                                        @i
                                FROM    dbo.Customers_Business
                                WHERE   ParentId = @ID 
                    END
                ELSE
                    BEGIN
                        INSERT  INTO @t
                                SELECT  Bid ,
                                        ParentId ,
                                        CONVERT(NVARCHAR(1000), Name) + '('
                                        + CONVERT(NVARCHAR(1000), Bid) + ')' ,
                                        @i
                                FROM    dbo.Customers_Business
                                WHERE   ParentId = @ID 
                    END
             
  
                WHILE @@rowcount <> 0
                    BEGIN 
                        SET @i = @i + 1 
                        INSERT  INTO @t
                                SELECT  a.Bid ,
                                        a.ParentId ,
                                        a.Name ,
                                        @i
                                FROM    dbo.Customers_Business a ,
                                        @t b
                                WHERE   a.ParentId = b.ID
                                        AND b.Level = @i - 1 
                    END 
            END  
        RETURN

    END