ALTER PROCEDURE [dbo].[GetAllChildID]
    @ParentID INT,
    @ChildIDStr VARCHAR(500) OUTPUT
AS
    DECLARE @i int
    DECLARE @startIndex int
    DECLARE @length int
    DECLARE @SubId  int
    DECLARE @SubStr VARCHAR(500)
 
    SET @ChildIDStr=''
    SELECT @ChildIDStr=@ChildIDStr+CAST(ID AS VARCHAR(5))+',' FROM dbo.Product WHERE ParentID=@ParentID
    IF (@ChildIDStr = '')
        RETURN
        
    SET @i = 1
    SET @length = LEN(@ChildIDStr)
    -- 循环
    WHILE( @i < @length)
    BEGIN
        --保存ID开始的索引
        SET @startIndex = @i
        --得到ID结束的索引
        WHILE((SUBSTRING(@ChildIDStr,@i,1) <> ','))
            SET @i = @i + 1
        --得到ID
        SET @SubId = CONVERT(INT,SUBSTRING(@ChildIDStr,@startIndex,@i-@startIndex))
        --初始化字符串
        SET @SubStr = ''
        --递归调用
        EXECUTE dbo.GetAllChildID @SubId,@SubStr OUTPUT
        if @SubStr <> ''
            SET @ChildIDStr = @ChildIDStr +@SubStr + ','
        --指向下一个字符
        SET @i = @i + 1
    END
    --去掉最后一个','
    SET @ChildIDStr = LEFT(@ChildIDStr,LEN(@ChildIDStr)-1)
GO

------解决方案--------------------
SQL code

--创建测试表
CREATE TABLE TableName
(
    [ID] INT PRIMARY KEY NOT NULL,
    [ParentID] INT NOT NULL,
    [Name] NVARCHAR(50)
)

--插入测试数据
INSERT INTO TableName([ID],[ParentID]) VALUES(1,0)
INSERT INTO TableName([ID],[ParentID]) VALUES(2,1)
INSERT INTO TableName([ID],[ParentID]) VALUES(3,1)
INSERT INTO TableName([ID],[ParentID]) VALUES(4,2)
INSERT INTO TableName([ID],[ParentID]) VALUES(5,3)
INSERT INTO TableName([ID],[ParentID]) VALUES(6,2)
INSERT INTO TableName([ID],[ParentID]) VALUES(7,2)
INSERT INTO TableName([ID],[ParentID]) VALUES(8,7)
INSERT INTO TableName([ID],[ParentID]) VALUES(9,3)
INSERT INTO TableName([ID],[ParentID]) VALUES(10,6)
GO

--创建存储过程
CREATE PROCEDURE dbo.GetAllChildID
@ParentID INT,
@ChildIDStr VARCHAR(500) OUTPUT
AS
SET @ChildIDStr=''
IF @ParentID IS NULL RETURN
DECLARE @OldRecordCnt INT
DECLARE @CurRecordCnt INT
CREATE Table #tmp ([ID] INT PRIMARY KEY NOT NULL)
INSERT INTO #tmp VALUES(@ParentID)
SET @OldRecordCnt=1
WHILE 1=1
BEGIN
    INSERT INTO #tmp SELECT a.[ID] FROM TableName a INNER JOIN #tmp b ON a.[ParentID]=b.[ID] 
    WHERE a.[ID] 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 @ChildIDStr=@ChildIDStr++CAST(ID AS VARCHAR(4))+',' FROM #tmp
IF @ChildIDStr<>'' 
    SET @ChildIDStr=LEFT(@ChildIDStr,LEN(@ChildIDStr)-1)
DROP TABLE #tmp
GO


--查询测试
DECLARE @ParentID INT
DECLARE @ChildIDStr VARCHAR(500)
SET @ParentID=2
EXEC dbo.GetAllChildID @ParentID,@ChildIDStr OUTPUT
SELECT @ChildIDStr

----------------------------------------------------
--测试结果
4,6,7,8,10

------解决方案--------------------
楼上正解，效率应该比用递归高吧。。

SQL代码规范。。