Recursive Query In Sql Server

A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.

A recursive CTE can greatly simplify the code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. In earlier versions of SQL Server, a recursive query usually requires using temporary tables, cursors, and logic to control the flow of the recursive steps.

The structure of the recursive Query

The structure of the recursive CTE in Transact-SQL is similar to recursive routines in other programming languages. Although a recursive routine in other languages returns a scalar value, a recursive CTE can return multiple rows.

A recursive CTE consists of three elements:

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.

Recursive query in sql server Example:

WITH  CTE(categoryId,CategoryName,ParentCategoryID,TopParentCategoryID) AS

(

SELECT    

a.CategoryId,

a.CategoryName,

a.ParentCategoryID, 

a.CategoryId AS TopParentCategoryID

FROM Categories  a WHERE  ParentCategoryID is null

UNION ALL

SELECT c.CategoryId, c.CategoryName, c.ParentCategoryID, cte.TopParentCategoryID AS TopParentCategoryID FROM Categories c,cte

WHERE cte.CategoryId = c.ParentCategoryID

)

SELECT CTE.categoryId,cte.CategoryName,cte.ParentCategoryID,cte.TopParentCategoryID FROM CTE