Coluna calculada do SQL Server para representar estrutura recursiva


1- Problema

Considerando que temos uma tabela com registros que representam as áreas do site, organizados de forma hierárquica e uma área pode ser filha de outra área, teremos algo como:

AreaID AreaPaiID Titulo
1 NULL Blog
2 NULL Site
3 1 Desenvolvimento
4 3 ASP.NET

A questão é:

Como fazer para exibir o caminho completo do registro da área 4 (/Blog/Desenvolvimento/ASP.NET) quando o registro for retornado do banco de dados?

2- Resolvendo com function e campo calculado

Um sugestão para resolver esse problema, é criar uma fuction recursiva que obtém o caminho de cada área e adicionar a chamada à essa function em um campo calculado na tabela.

O código da function é o seguinte:

CREATE FUNCTION [dbo].[ObtemCaminhoCompletoArea]
(
	@areaID INT,
	@separador VARCHAR(MAX),
	@nivel INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN

	DECLARE @areaPaiID INT
	DECLARE @titulo VARCHAR(200)
	DECLARE @ret VARCHAR(MAX)
	
	SELECT
		@areaPaiID = AreaPaiID,
		@titulo = titulo
	FROM
		Area (NOLOCK)
	WHERE
		Area.AreaID = @areaID
	
	SET @ret = ''
	
	IF (@nivel <= 1)
		SET @ret = @titulo + @separador
	
	IF (@areaPaiID IS NULL)
		SET @ret = @ret + @titulo
	ELSE
		SET @ret = @ret + dbo.ObtemCaminhoCompletoArea(@areaPaiID, @separador, @nivel + 1) + @separador + @titulo 
	
	RETURN @ret
END
GO

Dica: Dá para alterar essa function para fazer esse select recursivo utilizando o comando WITH

E adicione uma coluna calculada na tabela, chamando essa function:

ALTER TABLE Area ADD CaminhoCompleto AS (dbo.ObtemCaminhoCompletoArea(AreaID,'\', null))

Agora, ao selecionar os registros da tabela:

select * from Area

Na nova coluna, é exibido o caminho de cada área:

AreaID AreaPaiID Titulo CaminhoCompleto
1 NULL Blog Blog
2 NULL Site Site
3 1 Desenvolvimento Blog/Desenvolvimento
4 3 ASP.NET Blog/Desenvolvimento/ASP.NET

Comentários