ALTER FUNCTION DBO.F_GetAllChildID(@Pid INT) RETURNS VARCHAR(500) AS BEGIN DECLARE @T TABLE(ID INT,PARENTID INT,LEV INT) DECLARE @LEV INT SET @LEV = 1 INSERT INTO @T SELECT ID,PARENTID,[LEVEL] FROM ProductType WHERE ID = @Pid WHILE(@@ROWCOUNT > 0) BEGIN SET @LEV = @LEV+1 INSERT INTO @T SELECT A.ID,A.PARENTID,@LEV FROM ProductType A,@T B WHERE A.PARENTID = B.ID AND B.LEV = @LEV - 1 END DECLARE @CIDS VARCHAR(500) SELECT @CIDS = ISNULL(@CIDS+',','')+LTRIM(ID) FROM @T ORDER BY LEV RETURN @CIDS END