MSSQL 모든 상위 부서 찾기 프로시저

사원코드로 최상위 부서까지 모든 상위 부서를 구해야 할 때 사용한다.

기본적인 하이라키 구조 예제는 많은데

역순 하이라키 구조 예제가 별로 없어서 기록을 남겨둠

SOME_TABLE 이라는 가상의 테이블을 대상으로 하였다.

주요 컬럼은 ID, P_ID, CODE 이며

ID, P_ID 는 부모/자식의 하이라키 구조 핵심

CODE 는 사원코드/부서코드 중 하나이다.

즉, SOME_TABLE은 부서와 사원 정보가 같이 들어 있는 테이블이다.

부서 코드만 있는 구조라면 

WHERE CODE = @P_CODE 부분을 적절히 바꿔주면 되겠다.

IF OBJECT_ID('SP_PROC') IS NOT NULL

DROP PROC SP_PROC

GO

CREATE PROC SP_PROC

(

@P_CODE VARCHAR(32)

)

AS

BEGIN

SET NOCOUNT ON

;WITH CTE AS

(

SELECT 

A.ID, A.P_ID, A.COL1, A.COL2, A.COL3, A.CODE

FROM SOME_TABLE  A

WHERE CODE = @P_CODE

UNION ALL

SELECT 

A.ID, A.P_ID, A.COL1, A.COL2, A.COL3, A.CODE

FROM SOME_TABLE A

INNER JOIN CTE B

ON A.ID = B.P_ID

AND A.ID <> ISNULL(A.P_ID, -99) -- 최상위 부서의 P_ID는 NULL임

)

SELECT 

A.*,

B.TITLE

FROM CTE A

OPTION (MAXRECURSION 0) -- 하이라키 쿼리 시 100 넘는 에러 방지

END

GO