하나의 테이블로 무한카테고리 구현 하신분 어떻게 구현하는지 답글좀 주시면 감사하겠습니다.

정말 null 나올때까지 외부 어플에서 무한루프 돌려요?

--파라미터로 받은 코드를 기준으로 계층구조를 뽑아서

--테이블에 저장하는함수입니다. 적당히 고쳐 보세요. 

--sec_up_code 상위부서코드, sec_code 부서코드

CREATE FUNCTION HWF_TBL_SEC_CASCADE_FROM_CODE  (@current varchar(32))
RETURNS @rtn_table table(sec_code varchar(32), lvl int)
AS
Begin

-- SET NOCOUNT ON
 DECLARE @lvl int;
DECLARE @line varchar(32);
DECLARE @strRow varchar(1000);
DECLARE @tmp_table table(sec_code varchar(32), lvl int);

SET @strRow = ''
 
--CREATE TABLE #stack (item varchar(32), lvl int) --Create a tempory stack.
--CREATE TABLE @tmp_table (item varchar(32), lvl int) 
-- INSERT INTO #stack VALUES (@current, 1) --Insert current node to the stack.
 INSERT INTO @tmp_table VALUES (@current, 1) 
 SELECT @lvl = 1    
 WHILE @lvl > 0     --From the top level going down.
 BEGIN
 --    IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
    IF EXISTS (SELECT * FROM  @tmp_table WHERE lvl = @lvl)
         BEGIN
             SELECT @current = sec_code          --Find the first node that matches current node's name.
 --            FROM #stack
                       FROM @tmp_table
             WHERE lvl = @lvl
--PRINT 1
 --            SELECT @line = space(@lvl - 1) + @current --@lvl - 1 s spaces before the node name.
   SELECT @line =  @current
 --            PRINT @line     --Print it.
  SET @strRow = @strRow + ', ' + @line
   INSERT INTO @rtn_table VALUES (@line, @lvl - 1) 

 --            DELETE FROM #stack
                        DELETE FROM @tmp_table
             WHERE lvl = @lvl
                 AND sec_code = @current --Remove the current node from the stack.

        --    INSERT #stack  --Insert the childnodes of the current node into the stack.
   INSERT  @tmp_table
                  SELECT sec_code, @lvl + 1
                 FROM hw_section
                 WHERE sec_up_code = @current
             IF @@ROWCOUNT > 0  --If the previous statement added one or more nodes, go down for its first child.
                        SELECT @lvl = @lvl + 1 --If no nodes are added, check its brother nodes.
  END
     ELSE
  SELECT @lvl = @lvl - 1  --Back to the level immediately above.
END
return
end

--함수 테스트용
--EXEC HWF_TBL_SEC_CASCADE_FROM_CODE  '23021000'

--위에서 참고햇던 원본입니다.아마 msdn에서 제공하는 샘플이엇던듯..

CREATE TABLE hierarchy
        (parent VARCHAR(20) NOT NULL,
         child VARCHAR(20),
CONSTRAINT UIX_parentchild
         UNIQUE NONCLUSTERED (parent,child)
)
CREATE CLUSTERED INDEX CIX_parent
 ON hierarchy(parent)
GO
INSERT hierarchy VALUES('World','Europe')
INSERT hierarchy VALUES('World','North America')
INSERT hierarchy VALUES('Europe','France')
INSERT hierarchy VALUES('France','Paris')
INSERT hierarchy VALUES('North America','United States')
INSERT hierarchy VALUES('North America','Canada')
INSERT hierarchy VALUES('United States','New York')
INSERT hierarchy VALUES('United States','Washington')
INSERT hierarchy VALUES('New York','New York City')
INSERT hierarchy VALUES('Washington','Redmond')
GO

CREATE PROCEDURE expand (@current char(20)) as --This is a non-recursive preorder traversal.
 SET NOCOUNT ON
 DECLARE @lvl int, @line char(20)

 CREATE TABLE #stack (item char(20), lvl int) --Create a tempory stack.
 INSERT INTO #stack VALUES (@current, 1) --Insert current node to the stack.
 SELECT @lvl = 1    
 WHILE @lvl > 0     --From the top level going down.
 BEGIN
     IF EXISTS (SELECT * FROM #stack WHERE lvl = @lvl)
         BEGIN
             SELECT @current = item --Find the first node that matches current node's name.
             FROM #stack
             WHERE lvl = @lvl

             SELECT @line = space(@lvl - 1) + @current --@lvl - 1 s spaces before the node name.
             PRINT @line     --Print it.

             DELETE FROM #stack
             WHERE lvl = @lvl
                 AND item = @current --Remove the current node from the stack.

             INSERT #stack  --Insert the childnodes of the current node into the stack.
                 SELECT child, @lvl + 1
                 FROM hierarchy
                 WHERE parent = @current

             IF @@ROWCOUNT > 0  --If the previous statement added one or more nodes, go down for its first child.
                        SELECT @lvl = @lvl + 1 --If no nodes are added, check its brother nodes.
   END
     ELSE
       SELECT @lvl = @lvl - 1  --Back to the level immediately above.
        
END       --While

GO
EXEC expand 'World'

계층형 게시판의 로직을 쓰면 간단히 되지 않을까요?

무한, 유한 다 가능할텐데... ^^

아~ 넘 어려버~

커넥티드바이는 왜 안되는기야...ㅠ.ㅠ