创建唯一列表的Sql递归查询

出于速度原因,我需要将一些代码从C#移到存储过程中。我试图从基于CategoryId的RoleTemplates(或CategoryToRoleTemplate)表中获得一个唯一的templateId列表。

理想情况下,结果应该是RoleTemplate.templateId的唯一列表。

Categories
------------------------------
CategoryId    uniqueidentifier
ParentId      uniqueidentifier <-- Relationship to Categories.CategoryId.
Name          varchar (50)

CategoryToRoleTemplate
------------------------------
CategoryId    uniqueidentifier <-- Relationship to Categories.CategoryId.
TemplateId    uniqueidentifier <-- Relationship to RoleTemplates.TemplateId.

RoleTemplates
------------------------------
TemplateId    uniqueidentifier
Name          varchar (50)

我使用的是SQLServer2008R2。

编辑:

最终解决方案:

with CategoryHierarchy (ParentId)
as (
    -- Anchor member definition
    select CategoryId from Categories
    where CategoryId = @id
    union all

    -- Recursive member definition
    (select c.ParentId from Categories as c
        inner join CategoryHierarchy as p
        on c.CategoryId = p.ParentId)
)

select distinct TemplateId from CategoryToRoleTemplates where CategoryId in (select CategoryId from CategoryHierarchy);

感谢所有的回答!CTE是关键。