SQLINFO – We play with data and technology

• Common Table Expression (CTE)

               CTE  can be used as a temporary storage like temp tables,variables.A CTE is able provides a facility to refer itself.A recursive CTE is one which an intial CTE is executed repeatedly to return the subset of data until complete resultset is obtained.

• Structure of the CTE

A recursive CTE consists of three elements(As per MSDN):

1. Invocation of the routine.The first invocation of the recursive CTE consists of one or more CTE_query_definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members.CTE_query_definitions are considered anchor members unless they reference the CTE itself. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.
2. Recursive invocation of the routine.The recursive invocation includes one or more CTE_query_definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.
3. Termination check.The termination check is implicit; recursion stops when no rows are returned from the previous invocation.

CREATE TABLE [dbo].[EmployeeDetails]

  (

     [EmpID]        [INT] IDENTITY(1, 1) NOT NULL,

     [EmployeeName] [VARCHAR](50) NULL,

     [ManagerID]    [VARCHAR](50) NULL,

     [Designation]  [VARCHAR](50) NULL,

     CONSTRAINT [PK_EmployeeDetails] PRIMARY KEY CLUSTERED ( [EmpID] ASC )WITH (

     pad_index = OFF, statistics_norecompute = OFF, ignore_dup_key = OFF,

     allow_row_locks = on, allow_page_locks = on) ON [PRIMARY]

  )

ON [PRIMARY]

GO 

We can insert some data into the table,here for example I am using an IT company Herirachy

 

INSERT INTO dbo.employeedetails

VALUES      (‘E1’,NULL, ‘CheifTechnologyOfficer’),

            (‘E2’,1, ‘ProjectManager’),

            (‘E3’, 2,  ‘TeamLead’),

            (‘E4’,1,‘Sr.NetworkAdministrator’),

            (‘E5’, 2, ‘DBA’),

            (‘E6’,3, ‘Developer’) 

I am issuing  Select * from dbo.employeedetails

 

 

 

 

 

we  can use CTE to return the hierarchical structure of  company.just look into this.

WITH cte(empid, employeename, designation, managerid, [Level] )

     AS (

        –Defining Anchor Member

        SELECT empid,

               employeename,

               designation,

               managerid,

               0 AS [Level]

        FROM   dbo.employeedetails

        WHERE  managerid IS NULL

         —

         UNION ALL

         –Recursive Member Defenition

         SELECT e.empid,

                e.employeename,

                e.designation,

                e.managerid,

                cte.[Level] + 1

         FROM   dbo.employeedetails AS e

                INNER JOIN cte

                  ON e.managerid = cte.empid)

SELECT * FROM   cte