Recursividade no T-SQL

Hoje vamos exemplificar como implementar uma query recursiva, com o uso do comando WITH no MS-SQL. O comando WITH permite a criação de um result set temporário e nomeado, e pode ser usado dentro de um comando SELECT, INSERT, UPDATE, MERGE, ou DELETE, podendo ser usado inclusive em um comando CREATE VIEW.
O grande ganho do uso deste comando é a possibilidade de referenciar a si mesmo, que possibilita implementar a recursividade. Esta possibilidade é bastante útil quando precisamos implementar estruturas hierárquicas.
Vamos imaginar uma situação simples, onde temos departamentos de uma empresa e funcionários vinculados a ela:

create table departamento(
codigo int identity(1,1) primary key,
descricao varchar(100)
)
create table funcionarios(
codigo int identity(1,1) primary key,
nome varchar(100),
coddepartamento int
references departamento(codigo),
flativo char(1)
)

Agora vamos inserir alguns dados para teste:

insert into departamento(descricao) values('RH')
insert into departamento(descricao) values('Desenvolvimento')
insert into departamento(descricao) values('Comercial')
insert into funcionarios(nome,coddepartamento,flativo) values('Rodrigo Modzinsk',1,'S')
insert into funcionarios(nome,coddepartamento,flativo) values('Daniel Galeão',1,'S')
insert into funcionarios(nome,coddepartamento,flativo) values('Carolina Zaruba',2,'S')
insert into funcionarios(nome,coddepartamento,flativo) values('Maria Ulveseth',2,'S')
insert into funcionarios(nome,coddepartamento,flativo) values('Evelise Esmênia',3,'S')
insert into funcionarios(nome,coddepartamento,flativo) values('Joana da Silva',3,'S')

Agora vamos imaginar que nosso gerente do RH solicitou um relatório com todos os departamentos e funcionários vinculados a cada departamento. Podemos implementar isto facilmente com o comando WITH, fazendo referência a um dos campos de resultado.

with FuncionariosDepartamento(cdDepartamento, cdFuncionario, deDepartamento,nmFuncionario,flAtivo) as (
select
codigo as cdDepartamento,
0 as cdFuncionario,
descricao,
cast(' ' as varchar(100)) as nmfuncionario,
cast(' ' as char(1)) as flAtivo
from departamento
union all
select
0 as cdDepartamento,
codigo as cdFuncionario,
fd.deDepartamento,
' ' - nome,
f.flativo
from
funcionarios f
inner join FuncionariosDepartamento fd on (fd.cddepartamento=f.codDepartamento)
)

select * from FuncionariosDepartamento
order by deDepartamento,nmfuncionario