Recursive CTE

-------------------------Recursive CTE--------------------

SELECT * INTO #TEMP FROM EMP WHERE 1=2

DECLARE @mgr int

SELECT @mgr=mgr FROM EMP WHERE empno=7369

WHILE(@mgr IS NOT NULL)

BEGIN

INSERT INTO #TEMP SELECT * FROM EMP WHERE empno=@mgr

SELECT @mgr=mgr FROM EMP WHERE empno=@mgr

END

SELECT * FROM #TEMP

-------------------------------------------

;WITH X AS

( 

SELECT * FROM EMP WHERE EMPNO=7839

UNION ALL

SELECT E.* FROM X INNER JOIN EMP E ON X.EMPNO=E.MGR

)

SELECT * FROM X