SQL Server CTE Introduction

CTE or Common Table Expressions, are a new construct introduced in Microsoft SQL Server 2005 that offer a more readable form of the derived table and can be declared once and referenced multiple times in a query. One major advantage over derived table is, CTEs can be recursively defined and so it eliminates the need for recursive stored procedures.

In general, we use CTE for data selection only. But a CTE can be used for INSERT, UPDATE, DELETE and MERGE operations as well. Like derived table, a CTE can be used in functions, stored procedures, triggers, or views. In other words, CTE is temporary named result set of a query and defined within the execution scope of a single SELECT, INSERT, UPDATE, MERGE, or DELETE statement.

The general syntax of a CTE is:

[ WITH <common_table_expression> [ ,...n ] ]
<common_table_expression>::=
   expression_name [ ( column_name [ ,...n ] ) ]
   AS
   ( CTE_query_definition )

I will write more on using CTE but for today, have a look into a recursive CTE to get employee level of employee from employee table.

-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
    EmployeeID smallint NOT NULL,
    FirstName nvarchar(30)  NOT NULL,
    LastName  nvarchar(40) NOT NULL,
    Title nvarchar(50) NOT NULL,
    DeptID smallint NOT NULL,
    ManagerID int NULL,
 CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC) 
);
  
;WITH DirectReports( EmployeeID, Title, EmployeeLevel) AS
(
    SELECT  EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.MyEmployees 
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT  e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.MyEmployees AS e
        INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID 
)
SELECT EmployeeID, Title, EmployeeLevel 
FROM DirectReports
ORDER BY EmployeeLevel;

Regards

Rakesh Mishra

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook