TSQL for Recursive Select Using Common Table Expressions

Revision: 5667

at March 24, 2008 18:00 by rengber

Initial Code

WITH RecursiveCategories (CategoryHint, CategoryID, CatName, CatLevel, SearchKeywords, ParentCategoryId, ePagesOID) AS
(
  Select I.CategoryHint,
         C.CategoryId,
         C.Name as CatName,
         C.CatLevel, 
         C.SearchKeywords,
         C.ParentCategoryId, 
         I.ePagesOID
  From Core.Inventory I 
  left join Core.ProductBase PB on PB.ProductId = I.ProductId
  left join Core.ProductCategory PC on PC.ProductId = PB.ProductId
  left join Core.Category C on C.CategoryId = PC.CategoryID
  Where I.ePagesOID = @ePagesId
UNION ALL 
  Select RC.CategoryHint,
         C.CategoryId,
         C.Name as CatName,
         C.CatLevel, 
         C.SearchKeywords, 
         C.ParentCategoryId,
         RC.ePagesOID
  From Core.Category C 
  join RecursiveCategories RC on C.CategoryID = RC.ParentCategoryID
  Where RC.ePagesOID = @ePagesId  
  and RC.ParentCategoryId is not null
)
Select CategoryHint, CatName, CatLevel, SearchKeywords, CategoryId, ParentCategoryId from RecursiveCategories ORDER BY CatLevel

Initial URL

http://www.sqlservercentral.com/articles/Development/recursivequeriesinsqlserver2005/1760/

Initial Description

Initial Title

TSQL for Recursive Select Using Common Table Expressions

Initial Tags

sql

Initial Language

SQL

Choose a language for easy browsing: