Problema: Necesitamos una mejor manera de implementar consultas recursivas en SQL Server.

Solución: La Expresión de Tabla Común (CTE) proporciona una solución para implementar consultas recursivas en SQL Server. CTE se puede pensar como un conjunto de resultados temporales que se define dentro del ámbito de ejecución de una única instrucción SELECT, INSERT, UPDATE, DELETE o CREATE VIEW. Es una versión mejorada de las tablas derivadas que se asemeja estrechamente a un tipo de vista no persistente. Las CTE se pueden utilizar de muchas de las mismas formas que las tablas derivadas y también pueden contener referencias a sí mismas, lo que facilita la escritura de consultas complejas.

Ventajas de utilizar CTE:

• Las consultas con definiciones de tablas derivadas se vuelven más simples y legibles.
• Las CTE reducen significativamente la cantidad de código requerido para una consulta que recorre jerarquías recursivas.

Para crear una CTE en SQL Server, puedes utilizar la siguiente sintaxis:

WITH cte_alias (column_aliases)
AS
(
cte_query_definition -- inicialización
UNION ALL
cte_query_definition2 -- ejecución recursiva
)
SELECT * FROM cte_alias

Aquí tienes un ejemplo de una CTE que utiliza recursión:

WITH Managers AS
(
-- inicialización
SELECT EmployeeID, LastName, ReportsTo
FROM Employees
WHERE ReportsTo IS NULL
UNION ALL
-- ejecución recursiva
SELECT e.EmployeeID, e.LastName, e.ReportsTo
FROM Employees e
INNER JOIN Managers m ON e.ReportsTo = m.EmployeeID
)
SELECT * FROM Managers

En este ejemplo, la CTE “Managers” se utiliza para averiguar quién reporta a quién en la tabla Employees de la base de datos Northwind. La consulta de inicialización devuelve el resultado base, que es el nivel más alto en la jerarquía (identificado por el valor ReportsTo NULL). La consulta de ejecución recursiva se une a la consulta de inicialización utilizando la palabra clave UNION ALL, y el conjunto de resultados se basa en los subordinados directos devueltos por la consulta de inicialización. La recursión ocurre porque la consulta hace referencia a la CTE misma basada en el Empleado en la CTE Managers como entrada.

También puedes utilizar la sugerencia de consulta MAXRECURSION para detener una instrucción después de un número definido de bucles. Esto puede evitar que una CTE entre en un bucle infinito en una instrucción mal codificada. Para utilizar la sugerencia MAXRECURSION, puedes incluirla en la consulta SELECT que hace referencia a la CTE. Por ejemplo:

SELECT * FROM Managers OPTION (MAXRECURSION 4)

Si quieres lograr un resultado similar sin utilizar una CTE, tendrías que escribir una consulta más compleja utilizando variables de tabla y bucles. Aquí tienes un ejemplo:

DECLARE @rowsAdded INT

-- variable de tabla para almacenar los resultados acumulados
DECLARE @managers TABLE
(
EmpID INT,
MgrID INT,
LastName nvarchar(20),
processed INT DEFAULT(0)
)

INSERT @managers
SELECT EmployeeID, ReportsTo, LastName, 0
FROM Employees
WHERE ReportsTo IS NULL

SET @rowsAdded = @@ROWCOUNT

-- hacer esto mientras se agreguen nuevos empleados en la iteración anterior
WHILE @rowsAdded > 0
BEGIN
-- marcar registros de empleados que se encontrarán en esta iteración con processed=1
UPDATE @managers SET processed = 1 WHERE processed = 0

-- insertar empleados que reportan a empleados aún no procesados
INSERT @managers
SELECT EmployeeID, ReportsTo, e.LastName, 0
FROM Employees e
INNER JOIN @managers r ON e.ReportsTo = r.EmpID
WHERE ReportsTo <> EmployeeID AND r.processed = 1

SET @rowsAdded = @@ROWCOUNT

-- marcar registros de empleados encontrados en esta iteración como procesados
UPDATE @managers SET processed = 2 WHERE processed = 1
END

SELECT EmpID as EmployeeID, LastName, MgrID as ReportsTo FROM @managers

Sin embargo, utilizar una CTE proporciona una forma más legible y eficiente de lograr el mismo resultado.

En conclusión, las Expresiones de Tabla Comunes (CTEs) son una característica poderosa en SQL Server que permiten la implementación de consultas recursivas. Proporcionan una forma más simple y legible de trabajar con tablas derivadas y reducen significativamente la cantidad de código requerido para consultas que recorren jerarquías recursivas.