Tablas recursivas

Tablas recursivas

  19:30:00    Vladimir

---

Este es mi primer post, así que trataré de hacerlo lo más productivo posible; entre las curiosidades de SQL me encontré con un problema; al tener una tabla con recursividad no sabia como sacar todo el “arbol” de una forma dinámica, sin el uso de cursores o while.
Bueno, al caso; tengo una tabla jefe_usuario la cual tiene un campo cod_principal el cual será la PK (Primary Key) y un campo cod_jefe el cual será FK (Foreing Key) de la misma tabla.

Aquí tengo a todos mis usuarios, así como los jefes de cada uno de ellos. Aquí un ejemplo de los datos almacenados en la tabla:

cod_principal	nombre	cod_jefe
1	Juan	1
2	Pepe	1
3	Rolando	1
4	Enrique	2
5	Angel	3
6	Carlos	4
7	Maria	4
8	Vladimir	4
9	Santiago	5

Entonces lo que quiero saber es con el código principal del jefe, revisar los subordinados, hasta ahí fácil; pero también quiero saber los demás subordinados o sea, si quiero saber la información de Pepe al ejecutar el script la información me muestre algo parecido a esto:

• Juan
  • Pepe
    • Enrique
      • Carlos
      • María
      • Vladimir

Para esto vamos a utilizar un recurso de SQL que nos facilita la vida y nos permite dejar a un lado los cursores.
Entonces la sentencia sera algo parecido a esto

declare @cod_principal numeric = 2;

with cte_menu(id,padre,hijo)
as
(
 select mw.cod_principal, mw.cod_jefe, mw.nombre from jefe_usuario mw where mw.cod_principal = @cod_principal
 union all
 select SMW.cod_principal, SMW.cod_jefe, SMW.nombre from jefe_usuario SMW  INNER JOIN cte_menu CTM ON SMW.cod_jefe = CTM.id 
)

SELECT ct.id, jefe_usuario.nombre, ct.hijo FROM cte_menu ct inner join jefe_usuario on ct.padre = cod_principal ORDER BY ct.id,padre,ct.hijo

La semántica de la ejecución recursiva es la siguiente:

1. Dividir la expresión CTE en miembros delimitadores y recursivos.
2. Ejecutar los miembros no recursivos para crear la primera invocación o conjunto de resultados base (T0).
3. Ejecutar los miembros recursivos con Ti como entrada y Ti+1 como salida.
4. Repetir el paso 3 hasta que se devuelva un conjunto vacío.
5. Se devuelve el conjunto de resultados. Es una instrucción UNION ALL de T0 a Tn.

Ejecutando el script la información obtenida es la siguiente:

id	nombre	hijo
2	Juan	Pepe
4	Pepe	Enrique
6	Enrique	Carlos
7	Enrique	Maria
8	Enrique	Vladimir

“Puede que una CTE recursiva compuesta incorrectamente provoque un bucle infinito. Por ejemplo, si la definición de consulta del miembro recursivo devuelve los mismos valores para las columnas primarias y secundarias, se crea un bucle infinito. Al probar los resultados de una consulta recursiva, se puede limitar el número de niveles de recursividad permitidos para determinada instrucción mediante el uso de la sugerencia MAXRECURSION y un valor entre 0 y 32.767 en la cláusula OPTION de la instrucción INSERT, UPDATE, DELETE o SELECT.” Mas información acerca de recursividad aquí