오라클 재귀쿼리를 MSSQL 재귀쿼리로 만드는 방법

오라클 SQL 문

     SELECT MENU_CD_V     menucdv
     ,UP_MENU_CD_V  upmenucdv
     ,MENU_NM_V     menunmv
     ,DEPTH_N       depthn
     ,SHOW_ORDER_N  showordern
     ,USE_YN_C      useync
    FROM T_OM_USER_MENU
         START WITH UP_MENU_CD_V = '07000000'
           AND SITE_CD_V = 'S0000100'
       CONNECT BY PRIOR MENU_CD_V = UP_MENU_CD_V AND SITE_CD_V = 'S0000100'
         ORDER SIBLINGS BY SHOW_ORDER_N;

=========================================================================>

위의 오라클 sql 문을 ms-sql 문으로 변환

MS-SQL  문

WITH CTE_USER_MENU_TABLE
AS
(
   SELECT MENU_CD_V, UP_MENU_CD_V, MENU_NM_V, DEPTH_N, USE_YN_C , CONVERT(VARCHAR(255), RIGHT('000' + RTRIM(SHOW_ORDER_N),3) ) AS SHOW_ORDER_N
   FROM T_OM_USER_MENU
   WHERE UP_MENU_CD_V = '07000000' AND SITE_CD_V = 'S0000100'
   UNION ALL
   SELECT A.MENU_CD_V, A.UP_MENU_CD_V, A.MENU_NM_V, A.DEPTH_N, A.USE_YN_C , CONVERT(VARCHAR(255), CONVERT(VARCHAR,B.SHOW_ORDER_N) + '|' + RIGHT('000' + RTRIM(A.SHOW_ORDER_N),3) ) AS SHOW_ORDER_N
   FROM T_OM_USER_MENU A INNER JOIN CTE_USER_MENU_TABLE B ON A.UP_MENU_CD_V = B.MENU_CD_V
   WHERE A.SITE_CD_V = 'S0000100'
)
SELECT
    MENU_CD_V     menucdv
    ,UP_MENU_CD_V  upmenucdv
    ,MENU_NM_V     menunmv
    ,DEPTH_N       depthn
    ,SHOW_ORDER_N  showordern
    ,USE_YN_C      useync
   FROM CTE_USER_MENU_TABLE ORDER BY SHOW_ORDER_N;