asked on

CTE and CURSOR

hi exsperts,

WITH EmpOrg_CTE AS
(SELECT empid, mgrid, lastname, firstname --anchor query
      FROM HR.Employees
WHERE empid = 5 -- starting "top" of tree. Change this to show other root employees

UNION ALL
SELECT child.empid, child.mgrid, child.lastname, child.firstname -- recursive member which refers back to CTE
      FROM EmpOrg_CTE AS parent
      JOIN HR.Employees AS child
      ON child.mgrid=parent.empid
)
SELECT empid, mgrid, lastname, firstname
FROM EmpOrg_CTE;

can you gou givme this code with CURSOR?

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

enrique_aeo

ASKER

I just want to know what the code using a cursor

enrique_aeo

ASKER

would be a good example to show that you see using cursor should use a CTE?

enrique_aeo

ASKER

Please expert,you support
would be a good example to show that you see using cursor should use a CTE?

enrique_aeo

ASKER

hi experts,
experts, some other way to make this query. temporary tables?

WITH EmpOrg_CTE AS
(SELECT empid, mgrid, lastname, firstname --anchor query
FROM HR.Employees
WHERE empid = 5 -- starting "top" of tree. Change this to show other root employees

UNION ALL
SELECT child.empid, child.mgrid, child.lastname, child.firstname -- recursive member which refers back to CTE
FROM EmpOrg_CTE AS parent
JOIN HR.Employees AS child
ON child.mgrid=parent.empid
)
SELECT empid, mgrid, lastname, firstname
FROM EmpOrg_CTE;