[2k5] CTE 루프 제한 및 CTE를 활용한 업데이트

/********************************************************************************************
-- Title : [2k5] CTE 루프 제한 및 CTE를 활용한 업데이트
-- Reference : hanbitbook.co.kr, bol
--                  http://msdn.microsoft.com/ko-kr/library/ms175972.aspx
-- Key word : cte loop 루프 update 업데이트
*******************************************************************************************/
/*
** CTE 루프 제한
*/
USE AdventureWorks;
GO

--Creates an infinite loop
SET NOCOUNT ON;

WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte
    JOIN  HumanResources.Employee AS e
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO
--결과는 나오는데 다음의 오류를 반환한다.
--메시지 530, 수준 16, 상태 1, 줄 3
--문이 종료되었습니다. 문이 완료되기 전에 최대 재귀 횟수(2)가 초과되었습니다.
--
-- 이는 무한 루프를 방지하기 위한 제한이기에 에러 발생하고 결과를 롤백한다.
-- 0이면 무한, 1~32767까지 가능

/*
** CTE 활용한 업데이트
*/
USE AdventureWorks;
GO

WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS (SELECT e.EmployeeID, e.VacationHours, 1
    FROM HumanResources.Employee AS e
    WHERE e.ManagerID = 12
    UNION ALL
    SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
    FROM HumanResources.Employee as e
    JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
   )
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO