Menu Recursivo usando CTE (pt-BR)

Nota de Migração: Este artigo foi migrado para o Portal Wiki em Português. O conteúdo será atualizado aqui http://social.technet.microsoft.com/wiki/pt-br/contents/articles/12572.menu-recursivo-usando-cte.aspx.

Segue abaixo um código utilizado para recursividade de menu.

Criando o ambiente

Create table Menu

(

Codigo int identity(1,1) primary key,

Descricao varchar(50) not null,

CodigoPai int null

)

INSERT INTO Menu(Descricao) values('Arquivo'),('Editar'),('Ajuda')

INSERT INTO Menu(Descricao,CodigoPai) values('Salvar',1), ('Salvar Como',1),('Sair',1)

INSERT INTO Menu(Descricao,CodigoPai) values('Copiar',2), ('Colar',2),('Recortar',2)

INSERT INTO Menu(Descricao,CodigoPai) values('Sobre',3)

INSERT INTO Menu(Descricao,CodigoPai) values('PDF',5), ('Word',5),('Excel',5)

INSERT INTO Menu(Descricao,CodigoPai) values('Doc',12), ('Docx',12)

Execultando o comando

WITH cteMenu AS (

--Ancora

SELECT Codigo, Descricao, CodigoPai, Codigo AS Ordem, 1 AS Nivel

FROM Menu

WHERE CodigoPai IS NULL

UNION ALL

-- Consulta Recursiva

SELECT M.Codigo, M.Descricao, cte.Codigo, CASE WHEN M.Codigo < 10 THEN (Ordem * 10) + M.Codigo ELSE (Ordem*100) + M.Codigo END, Nivel +1

FROM cteMenu AS cte INNER JOIN Menu AS M ON cte.Codigo = M.CodigoPai

)

SELECT Replicate(' ', Nivel - 1) + Descricao MenuCompleto, Nivel FROM cteMenu

ORDER BY Cast(Ordem AS varchar)