/********************************************************************************************
-- Title : [2k5] CROSS APPLY 실 사용 예
-- Reference :
-- Key word : cross apply cte bom
********************************************************************************************/
-- 트리 구현은 CTE로 처리하는 함수를 만들어 놓고,
-- 공유(하위 포함/미포함)을 처리할 때 포함과 미포함에 맞춰 APPLY로 반영한 것.
-- 모.. 그냥하면 된다는거...ㅡㅡ;;
/*
-- 함수 생성
*/
CREATE FUNCTION [dbo].[fn_myfolder_shared_tree_select]
( @fldr_id INT -- 폴더_아이디
, @sect TINYINT -- 하위 폴더 포함 여부
)
RETURNS @fldr_list TABLE
( fldr_id INT
, hghr_fldr_id INT
, fldr_nm NVARCHAR(10)
, LEVEL INT
, fldr_ti NVARCHAR(MAX)
, pat_cnt INT
)
BEGIN
IF (@sect = 0)
/***************************************************
- 공유된 폴더의 하위 폴더 포함
**************************************************/
BEGIN
WITH FolderTreeCte(fldr_id, hghr_fldr_id, fldr_nm, LEVEL, sort, fldr_ti)
AS (SELECT fldr_id
, hghr_fldr_id
, fldr_nm
, 2
, CAST(fldr_nm + CAST(fldr_id AS NVARCHAR(10)) AS NVARCHAR(MAX))
, CAST(fldr_nm AS NVARCHAR(MAX))
FROM sch_xmyfolder.pat_fldr
WHERE fldr_id = @fldr_id
UNION ALL
SELECT a.fldr_id
, a.hghr_fldr_id
, a.fldr_nm
, c.LEVEL + 1 "LEVEL"
, CAST(c.sort + N' | '+ a.fldr_nm + CAST(a.fldr_id AS NVARCHAR(10)) AS NVARCHAR(MAX)) "fldr_sort"
/*a.fldr_nm만 가지고 정렬하면 특정 폴더를 루트로 복사시 정렬이 안되어 a.fldr_id를 붙였다.*/
, CAST(c.fldr_ti + N' > '+ a.fldr_nm AS NVARCHAR(MAX)) "fldr_ti"
FROM sch_xmyfolder.pat_fldr a
INNER JOIN FolderTreeCte c
ON a.hghr_fldr_id = c.fldr_id
)
INSERT INTO @fldr_list
SELECT cte.fldr_id, ISNULL(cte.hghr_fldr_id,0) "hghr_fldr_id", cte.fldr_nm
, cte.LEVEL, cte.fldr_ti + N'@|@' AS fldr_ti
, pat_cnt = (SELECT COUNT(*) FROM sch_xmyfolder.pat_list WHERE fldr_id = cte.fldr_id)
FROM FolderTreeCte cte
ORDER BY cte.sort;
END
ELSE
/***************************************************
- 공유된 폴더의 하위 폴더 미포함
**************************************************/
INSERT INTO @fldr_list
SELECT fldr_id
, hghr_fldr_id
, fldr_nm
, 2 "LEVEL"
, CAST(fldr_nm AS NVARCHAR(MAX)) "fldr_ti"
, pat_cnt = (SELECT COUNT(*) FROM sch_xmyfolder.pat_list WHERE fldr_id = @fldr_id)
FROM sch_xmyfolder.pat_fldr
WHERE fldr_id = @fldr_id;
RETURN
END;
GO
/*
-- 트리 구현
*/
SELECT d.membr_nm
, c.fldr_id
, c.hghr_fldr_id
, c.fldr_nm
, c.LEVEL
, c.fldr_ti
, c.pat_cnt
FROM sch_xmyfolder.pat_shr a
INNER JOIN sch_xmyfolder.pat_fldr b
ON a.fldr_id = b.fldr_id
INNER JOIN linked_seattle.WipsPlusdb.sch_xwipsplus.membr_info d
ON b.membr_cd = d.membr_cd
CROSS APPLY (SELECT fldr_id, hghr_fldr_id, fldr_nm, LEVEL, fldr_ti, pat_cnt
FROM dbo.fn_myfolder_shared_tree_select(a.fldr_id, a.lwr_fldr_incld_yn)
) c
WHERE a.membr_cd = @membr_cd;
GO