SQL Server (Recursivas)

 

Un CTE (Common Table Expression, por sus siglas en inglés) es una estructura en SQL que permite crear una consulta temporal que puede ser referenciada dentro de una instrucción más grande. Cuando se usa para recorrer una jerarquía organizacional, el CTE se define de manera recursiva para explorar relaciones entre filas en una tabla que tiene una estructura de tipo árbol o jerarquía.

En el contexto de una organización, un CTE recursivo te permite recorrer niveles jerárquicos, como empleados que dependen de un jefe, o regiones que pertenecen a una estructura más grande, sin necesidad de realizar múltiples consultas anidadas.

En SQL Server, puedes crear una tabla que represente una estructura recursiva, como los municipios que pueden estar relacionados jerárquicamente. Esto se logra definiendo una tabla con una relación de autorreferencia, donde un municipio puede tener un "municipio padre".

Cómo funciona un CTE recursivo

Un CTE recursivo tiene dos partes principales:

1. Caso base: Define el punto de inicio, es decir, el nivel más alto de la jerarquía (por ejemplo, el CEO en una organización).
2. Caso recursivo: Define cómo encontrar los niveles subordinados basándose en los resultados de la iteración previa.

Ventajas de usar un CTE recursivo para jerarquías

1. Simplicidad: Facilita escribir consultas que, de otro modo, requerirían lógica más compleja con bucles o consultas anidadas.
2. Escalabilidad: Permite manejar jerarquías de cualquier profundidad sin necesidad de especificar cuántos niveles recorrer.
3. Legibilidad: El código es más fácil de entender y mantener en comparación con soluciones alternativas.

Ejemplo Uno.

1. Crear la tabla

CREATE TABLE Municipios (
    MunicipioID INT PRIMARY KEY,
    Nombre VARCHAR(255) NOT NULL,
    MunicipioPadreID INT NULL, -- Clave foránea que apunta a la misma tabla
    CONSTRAINT FK_MunicipioPadre FOREIGN KEY (MunicipioPadreID)
    REFERENCES Municipios(MunicipioID)
);

2. Insertar datos de ejemplo

INSERT INTO Municipios (MunicipioID, Nombre, MunicipioPadreID)
VALUES
    (1, 'Municipio A', NULL), -- Municipio raíz
    (2, 'Municipio B', 1),    -- Municipio B depende de Municipio A
    (3, 'Municipio C', 1),    -- Municipio C depende de Municipio A
    (4, 'Municipio D', 2),    -- Municipio D depende de Municipio B
    (5, 'Municipio E', 3);    -- Municipio E depende de Municipio C

3. Consulta recursiva con Common Table Expressions (CTE)

Puedes usar una expresión común de tabla (CTE) para recorrer la jerarquía de manera recursiva.

Por ejemplo, para listar todos los municipios a partir de un nodo raíz:

WITH MunicipiosRecursivos AS (
    SELECT 
        MunicipioID,
        Nombre,
        MunicipioPadreID,
        0 AS Nivel
    FROM Municipios
    WHERE MunicipioPadreID IS NULL -- Nodo raíz

    UNION ALL

    SELECT 
        m.MunicipioID,
        m.Nombre,
        m.MunicipioPadreID,
        mr.Nivel + 1
    FROM Municipios m
    INNER JOIN MunicipiosRecursivos mr
        ON m.MunicipioPadreID = mr.MunicipioID
)
SELECT * 
FROM MunicipiosRecursivos
ORDER BY Nivel, Nombre;

Explicación:

1. Estructura de la tabla:

   • MunicipioID: Identificador único del municipio.
   • Nombre: Nombre del municipio.
   • MunicipioPadreID: Referencia al municipio superior en la jerarquía (puede ser NULL para los nodos raíz).

2. Consulta recursiva:

   • La primera parte del CTE selecciona los nodos raíz (MunicipioPadreID IS NULL).
   • La segunda parte se une recursivamente con los municipios hijos.

3. Campo Nivel:

   • Ayuda a identificar la profundidad de cada municipio en la jerarquía.

Resultado esperado (con los datos de ejemplo):

MunicipioID	Nombre	MunicipioPadreID	Nivel
1	Municipio A	NULL	0
2	Municipio B	1	1
3	Municipio C	1	1
4	Municipio D	2	2
5	Municipio E	3	2

Ejemplo dos.

Aquí tienes un ejemplo de cómo crear una tabla recursiva que represente los niveles administrativos de una organización, como una jerarquía de empleados, donde cada empleado puede tener un jefe (empleado padre).

1. Crear la tabla

CREATE TABLE Empleados (
    EmpleadoID INT PRIMARY KEY,
    Nombre VARCHAR(255) NOT NULL,
    Puesto VARCHAR(255) NOT NULL,
    EmpleadoPadreID INT NULL, -- Referencia al jefe directo
    CONSTRAINT FK_EmpleadoPadre FOREIGN KEY (EmpleadoPadreID)
    REFERENCES Empleados(EmpleadoID)
);

2. Insertar datos de ejemplo

INSERT INTO Empleados (EmpleadoID, Nombre, Puesto, EmpleadoPadreID)
VALUES
    (1, 'Ana Pérez', 'CEO', NULL),           -- Nivel superior (sin jefe)
    (2, 'Carlos Gómez', 'Gerente', 1),      -- Depende del CEO
    (3, 'Laura Martínez', 'Gerente', 1),    -- Depende del CEO
    (4, 'Pedro López', 'Supervisor', 2),    -- Depende del Gerente Carlos
    (5, 'María Díaz', 'Supervisor', 3),     -- Depende del Gerente Laura
    (6, 'Jorge Ramírez', 'Empleado', 4),    -- Depende del Supervisor Pedro
    (7, 'Claudia Sánchez', 'Empleado', 5);  -- Depende del Supervisor María

Aquí tienes una consulta básica y una consulta recursiva para la tabla Empleados que definiste. Esto te permitirá analizar la jerarquía organizacional completa.

Consulta básica

Si deseas ver todos los empleados y su relación con su jefe directo (si existe):

SELECT 
    e.EmpleadoID,
    e.Nombre AS Empleado,
    e.Puesto,
    j.Nombre AS Jefe,
    j.Puesto AS PuestoJefe
FROM Empleados e
LEFT JOIN Empleados j
    ON e.EmpleadoPadreID = j.EmpleadoID;

Resultado esperado:

EmpleadoID	Empleado	Puesto	Jefe	PuestoJefe
1	Ana Pérez	CEO	NULL	NULL
2	Carlos Gómez	Gerente	Ana Pérez	CEO
3	Laura Martínez	Gerente	Ana Pérez	CEO
4	Pedro López	Supervisor	Carlos Gómez	Gerente
5	María Díaz	Supervisor	Laura Martínez	Gerente
6	Jorge Ramírez	Empleado	Pedro López	Supervisor
7	Claudia Sánchez	Empleado	María Díaz	Supervisor

Consulta recursiva con CTE

Si deseas recorrer la jerarquía organizacional completa con niveles, puedes usar un CTE recursivo como este:

WITH Organizacion AS (
    -- Caso base: Empleados sin jefe (nivel más alto de la jerarquía)
    SELECT 
        EmpleadoID,
        Nombre,
        Puesto,
        EmpleadoPadreID,
        0 AS Nivel -- Nivel inicial (CEO)
    FROM Empleados
    WHERE EmpleadoPadreID IS NULL

    UNION ALL

    -- Caso recursivo: Encuentra subordinados
    SELECT 
        e.EmpleadoID,
        e.Nombre,
        e.Puesto,
        e.EmpleadoPadreID,
        o.Nivel + 1 -- Incrementa el nivel jerárquico
    FROM Empleados e
    INNER JOIN Organizacion o
        ON e.EmpleadoPadreID = o.EmpleadoID
)
SELECT 
    EmpleadoID,
    Nombre,
    Puesto,
    EmpleadoPadreID,
    Nivel
FROM Organizacion
ORDER BY Nivel, EmpleadoID;

Resultado esperado:

EmpleadoID	Nombre	Puesto	EmpleadoPadreID	Nivel
1	Ana Pérez	CEO	NULL	0
2	Carlos Gómez	Gerente	1	1
3	Laura Martínez	Gerente	1	1
4	Pedro López	Supervisor	2	2
5	María Díaz	Supervisor	3	2
6	Jorge Ramírez	Empleado	4	3
7	Claudia Sánchez	Empleado	5	3

Consulta de empleados subordinados de un jefe específico

Si necesitas ver todos los empleados que dependen de un jefe en particular (por ejemplo, EmpleadoID = 2):

WITH Subordinados AS (
    -- Caso base: Empleados directos del jefe
    SELECT 
        EmpleadoID,
        Nombre,
        Puesto,
        EmpleadoPadreID,
        0 AS Nivel
    FROM Empleados
    WHERE EmpleadoPadreID = 2 -- Cambiar por el ID del jefe

    UNION ALL

    -- Caso recursivo: Subordinados de los empleados encontrados
    SELECT 
        e.EmpleadoID,
        e.Nombre,
        e.Puesto,
        e.EmpleadoPadreID,
        s.Nivel + 1
    FROM Empleados e
    INNER JOIN Subordinados s
        ON e.EmpleadoPadreID = s.EmpleadoID
)
SELECT 
    EmpleadoID,
    Nombre,
    Puesto,
    EmpleadoPadreID,
    Nivel
FROM Subordinados
ORDER BY Nivel, EmpleadoID;

Resultado esperado (si el jefe es Carlos Gómez, EmpleadoID = 2):

EmpleadoID	Nombre	Puesto	EmpleadoPadreID	Nivel
4	Pedro López	Supervisor	2	0
6	Jorge Ramírez	Empleado	4	1

Para consultar un empleado específico en la tabla Empleados, puedes usar una cláusula WHERE para filtrar por el EmpleadoID, Nombre u otro criterio.

Ejemplo básico: Consultar por EmpleadoID

SELECT 
    EmpleadoID,
    Nombre,
    Puesto,
    EmpleadoPadreID
FROM Empleados
WHERE EmpleadoID = 4; -- Cambia el ID al del empleado que deseas consultar

Resultado esperado (si el empleado con EmpleadoID = 4 es Pedro López):

EmpleadoID	Nombre	Puesto	EmpleadoPadreID
4	Pedro López	Supervisor	2

Consultar por nombre

Si no conoces el EmpleadoID pero tienes el nombre del empleado, usa la cláusula WHERE para buscar por Nombre:

SELECT 
    EmpleadoID,
    Nombre,
    Puesto,
    EmpleadoPadreID
FROM Empleados
WHERE Nombre = 'Pedro López'; -- Cambia por el nombre del empleado

Nota: Si hay empleados con nombres duplicados, este filtro podría devolver múltiples resultados.

Consultar por información adicional del empleado y su jefe

Si deseas ver la información del empleado junto con la de su jefe directo, puedes hacer una auto-unión de la tabla:

SELECT 
    e.EmpleadoID,
    e.Nombre AS Empleado,
    e.Puesto AS PuestoEmpleado,
    j.Nombre AS Jefe,
    j.Puesto AS PuestoJefe
FROM Empleados e
LEFT JOIN Empleados j
    ON e.EmpleadoPadreID = j.EmpleadoID
WHERE e.EmpleadoID = 4; -- Cambia por el ID del empleado a consultar

Resultado esperado (si el empleado es Pedro López y su jefe es Carlos Gómez):

EmpleadoID	Empleado	PuestoEmpleado	Jefe	PuestoJefe
4	Pedro López	Supervisor	Carlos Gómez	Gerente

Consultar todos los subordinados de un empleado específico

Si deseas consultar no solo al empleado, sino también a todos sus subordinados, puedes usar un CTE recursivo:

WITH Subordinados AS (
    -- Caso base: Empleado específico
    SELECT 
        EmpleadoID,
        Nombre,
        Puesto,
        EmpleadoPadreID,
        0 AS Nivel
    FROM Empleados
    WHERE EmpleadoID = 2 -- Cambia por el ID del empleado a consultar

    UNION ALL

    -- Caso recursivo: Subordinados de los empleados encontrados
    SELECT 
        e.EmpleadoID,
        e.Nombre,
        e.Puesto,
        e.EmpleadoPadreID,
        s.Nivel + 1
    FROM Empleados e
    INNER JOIN Subordinados s
        ON e.EmpleadoPadreID = s.EmpleadoID
)
SELECT 
    EmpleadoID,
    Nombre,
    Puesto,
    EmpleadoPadreID,
    Nivel
FROM Subordinados
ORDER BY Nivel, EmpleadoID;

Resultado esperado (si el empleado es Carlos Gómez, EmpleadoID = 2):

EmpleadoID	Nombre	Puesto	EmpleadoPadreID	Nivel
2	Carlos Gómez	Gerente	1	0
4	Pedro López	Supervisor	2	1
6	Jorge Ramírez	Empleado	4	2

Para encontrar el jefe superior (el nivel más alto de la jerarquía) de un empleado, puedes usar un CTE recursivo para recorrer la cadena de relaciones de jefe a empleado hasta llegar al nodo raíz de la jerarquía (es decir, donde EmpleadoPadreID es NULL).

Consulta para encontrar el jefe superior

WITH JefeSuperior AS (
    -- Caso base: El empleado inicial
    SELECT 
        EmpleadoID,
        Nombre,
        Puesto,
        EmpleadoPadreID
    FROM Empleados
    WHERE EmpleadoID = 6 -- Cambia por el ID del empleado que deseas analizar

    UNION ALL

    -- Caso recursivo: Encuentra el jefe directo hasta llegar al jefe superior
    SELECT 
        e.EmpleadoID,
        e.Nombre,
        e.Puesto,
        e.EmpleadoPadreID
    FROM Empleados e
    INNER JOIN JefeSuperior j
        ON e.EmpleadoID = j.EmpleadoPadreID
)
-- Seleccionar solo el jefe superior
SELECT TOP 1 
    EmpleadoID,
    Nombre,
    Puesto
FROM JefeSuperior
WHERE EmpleadoPadreID IS NULL; -- El jefe superior es el que no tiene jefe

Explicación de la consulta

1. Caso base:

   • Inicia con el empleado cuyo jefe superior deseas encontrar (EmpleadoID = 6 en este caso).

2. Caso recursivo:

   • Busca al jefe directo (EmpleadoPadreID) del empleado actual y continúa subiendo la jerarquía.

3. Filtro final:

   • Selecciona el primer registro donde EmpleadoPadreID IS NULL, lo que indica el jefe superior.

Ejemplo con los datos de prueba

Si los datos de la tabla son:

EmpleadoID	Nombre	Puesto	EmpleadoPadreID
1	Ana Pérez	CEO	NULL
2	Carlos Gómez	Gerente	1
3	Laura Martínez	Gerente	1
4	Pedro López	Supervisor	2
5	María Díaz	Supervisor	3
6	Jorge Ramírez	Empleado	4

Al ejecutar la consulta con EmpleadoID = 6:

Resultado esperado:

EmpleadoID	Nombre	Puesto
1	Ana Pérez	CEO

Consultar jefe superior para múltiples empleados

Si deseas encontrar el jefe superior para todos los empleados, puedes modificar ligeramente la consulta para que recorra toda la tabla y muestre cada empleado junto con su jefe superior:

WITH JefeSuperior AS (
    -- Caso base: Todos los empleados
    SELECT 
        EmpleadoID,
        Nombre,
        Puesto,
        EmpleadoPadreID,
        EmpleadoID AS EmpleadoRaiz
    FROM Empleados

    UNION ALL

    -- Caso recursivo: Encuentra al jefe directo
    SELECT 
        e.EmpleadoID,
        e.Nombre,
        e.Puesto,
        e.EmpleadoPadreID,
        j.EmpleadoRaiz
    FROM Empleados e
    INNER JOIN JefeSuperior j
        ON e.EmpleadoID = j.EmpleadoPadreID
)
SELECT 
    j.EmpleadoRaiz AS EmpleadoID,
    e.Nombre AS NombreEmpleado,
    e.Puesto AS PuestoEmpleado,
    j.EmpleadoID AS JefeSuperiorID,
    j.Nombre AS NombreJefeSuperior,
    j.Puesto AS PuestoJefeSuperior
FROM JefeSuperior j
JOIN Empleados e
    ON j.EmpleadoRaiz = e.EmpleadoID
WHERE j.EmpleadoPadreID IS NULL; -- Selecciona solo los jefes superiores

Resultado esperado:

EmpleadoID	NombreEmpleado	PuestoEmpleado	JefeSuperiorID	NombreJefeSuperior	PuestoJefeSuperior
1	Ana Pérez	CEO	1	Ana Pérez	CEO
2	Carlos Gómez	Gerente	1	Ana Pérez	CEO
3	Laura Martínez	Gerente	1	Ana Pérez	CEO
4	Pedro López	Supervisor	1	Ana Pérez	CEO
5	María Díaz	Supervisor	1	Ana Pérez	CEO
6	Jorge Ramírez	Empleado	1	Ana Pérez	CEO

Para presentar a los jefes superiores de cada empleado por nivel jerárquico, podemos construir un CTE recursivo que recorra toda la jerarquía y devuelva todos los niveles de jefes desde un empleado específico o para todos los empleados.

Consulta para obtener jefes superiores por nivel jerárquico

WITH Jerarquia AS (
    -- Caso base: Cada empleado inicia con su propio registro
    SELECT 
        EmpleadoID,
        Nombre AS NombreEmpleado,
        Puesto AS PuestoEmpleado,
        EmpleadoPadreID,
        CAST(NULL AS INT) AS JefeID,
        CAST(NULL AS VARCHAR(255)) AS NombreJefe,
        CAST(NULL AS VARCHAR(255)) AS PuestoJefe,
        0 AS Nivel
    FROM Empleados

    UNION ALL

    -- Caso recursivo: Subir un nivel jerárquico en cada iteración
    SELECT 
        e.EmpleadoID,
        e.Nombre AS NombreEmpleado,
        e.Puesto AS PuestoEmpleado,
        e.EmpleadoPadreID,
        j.EmpleadoID AS JefeID,
        j.Nombre AS NombreJefe,
        j.Puesto AS PuestoJefe,
        j.Nivel + 1 AS Nivel
    FROM Empleados e
    INNER JOIN Jerarquia j
        ON e.EmpleadoPadreID = j.EmpleadoID
)
SELECT 
    EmpleadoID,
    NombreEmpleado,
    PuestoEmpleado,
    Nivel,
    JefeID,
    NombreJefe,
    PuestoJefe
FROM Jerarquia
WHERE JefeID IS NOT NULL -- Excluir los empleados sin jefes superiores
ORDER BY EmpleadoID, Nivel;

Explicación de la consulta

1. Caso base:

   • Cada empleado comienza con su propio registro, sin jefes asignados inicialmente (JefeID, NombreJefe y PuestoJefe son NULL).
   • Nivel inicial es 0.

2. Caso recursivo:

   • Para cada empleado, busca al jefe directo (EmpleadoPadreID) y sube un nivel.
   • Se agrega el nivel jerárquico de forma incremental (Nivel + 1).

3. Consulta final:

   • Muestra los empleados junto con sus jefes y el nivel jerárquico en que se encuentra cada jefe superior.
   • Se excluyen registros sin jefes (WHERE JefeID IS NOT NULL).

Ejemplo con datos de prueba

Tabla Empleados:

EmpleadoID	Nombre	Puesto	EmpleadoPadreID
1	Ana Pérez	CEO	NULL
2	Carlos Gómez	Gerente	1
3	Laura Martínez	Gerente	1
4	Pedro López	Supervisor	2
5	María Díaz	Supervisor	3
6	Jorge Ramírez	Empleado	4

Resultado esperado:

EmpleadoID	NombreEmpleado	PuestoEmpleado	Nivel	JefeID	NombreJefe	PuestoJefe
2	Carlos Gómez	Gerente	1	1	Ana Pérez	CEO
3	Laura Martínez	Gerente	1	1	Ana Pérez	CEO
4	Pedro López	Supervisor	1	2	Carlos Gómez	Gerente
4	Pedro López	Supervisor	2	1	Ana Pérez	CEO
5	María Díaz	Supervisor	1	3	Laura Martínez	Gerente
5	María Díaz	Supervisor	2	1	Ana Pérez	CEO
6	Jorge Ramírez	Empleado	1	4	Pedro López	Supervisor
6	Jorge Ramírez	Empleado	2	2	Carlos Gómez	Gerente
6	Jorge Ramírez	Empleado	3	1	Ana Pérez	CEO

Interpretación

• Cada empleado tiene asociado un registro por cada nivel de jefe superior en la jerarquía.
• Por ejemplo:
  • Jorge Ramírez tiene como jefe directo a Pedro López (Nivel 1), cuyo jefe es Carlos Gómez (Nivel 2), y finalmente llega a Ana Pérez (Nivel 3).

Modificar un registro en una tabla que tiene una relación recursiva (como la tabla Empleados) requiere considerar cuidadosamente cómo afecta el cambio en la relación jerárquica para evitar inconsistencias o referencias inválidas. A continuación, te explico cómo hacerlo y presento ejemplos.

Ejemplo: Modificar el jefe directo (EmpleadoPadreID)

Si deseas cambiar el jefe directo de un empleado, puedes usar un UPDATE para modificar el campo EmpleadoPadreID.

Caso 1: Cambiar jefe directo

Por ejemplo, si quieres cambiar el jefe de Jorge Ramírez (EmpleadoID = 6) para que reporte a Laura Martínez (EmpleadoID = 3):

UPDATE Empleados
SET EmpleadoPadreID = 3 -- Nuevo jefe
WHERE EmpleadoID = 6; -- Empleado a modificar

Validación previa

Antes de realizar este cambio, es importante verificar que:

1. El nuevo EmpleadoPadreID (3 en este caso) existe en la tabla.
2. El cambio no genera un ciclo en la jerarquía. Por ejemplo, que el empleado que estás actualizando no sea un ancestro del nuevo jefe, lo que rompería la estructura jerárquica.

Puedes verificar estos puntos con la siguiente consulta:

Verificar si el nuevo jefe existe:

SELECT *
FROM Empleados
WHERE EmpleadoID = 3; -- Nuevo jefe

Verificar que no se generen ciclos: Utiliza un CTE recursivo para obtener toda la cadena jerárquica del nuevo jefe y asegurarte de que el empleado no aparece como un ancestro.

WITH CadenaJerarquica AS (
    SELECT EmpleadoID, EmpleadoPadreID
    FROM Empleados
    WHERE EmpleadoID = 3 -- Nuevo jefe

    UNION ALL

    SELECT e.EmpleadoID, e.EmpleadoPadreID
    FROM Empleados e
    INNER JOIN CadenaJerarquica c
        ON e.EmpleadoID = c.EmpleadoPadreID
)
SELECT *
FROM CadenaJerarquica
WHERE EmpleadoID = 6; -- El empleado no debe aparecer en la cadena jerárquica

Si el resultado no devuelve registros, es seguro realizar la actualización.

Caso 2: Eliminar el jefe directo (dejar sin jefe)

Si deseas que un empleado no tenga jefe (es decir, moverlo al nivel superior), establece EmpleadoPadreID = NULL:

UPDATE Empleados
SET EmpleadoPadreID = NULL
WHERE EmpleadoID = 6; -- Empleado a modificar

Validar el cambio

Después de realizar el UPDATE, puedes verificar los resultados ejecutando:

SELECT 
    EmpleadoID,
    Nombre,
    Puesto,
    EmpleadoPadreID
FROM Empleados
WHERE EmpleadoID = 6; -- Verificar el empleado actualizado

Consecuencias de la modificación

1. Cascada jerárquica:

   • Cambiar un jefe afecta a todos los subordinados indirectos. Por ejemplo, si cambias el jefe de Carlos Gómez, los empleados que dependen jerárquicamente de él (directa o indirectamente) también cambian en la cadena.

2. Ciclos jerárquicos:

   • Asegúrate siempre de validar que el cambio no genere ciclos en la estructura jerárquica, lo que podría invalidar las consultas recursivas.

3. Impacto en las consultas:

   • Cualquier consulta basada en jerarquía debe reflejar correctamente los cambios realizados.

Si necesitas implementar una solución para validar automáticamente los cambios o añadir restricciones adicionales, 

Eliminar un jefe o un empleado en una tabla con jerarquía recursiva requiere atención especial, ya que puedes afectar la relación con otros empleados. Te muestro cómo manejar ambos casos:

Caso 1: Eliminar un jefe

Si deseas eliminar un jefe (EmpleadoPadreID), debes decidir qué hacer con sus subordinados.

Opción 1: Reasignar subordinados a un nuevo jefe

Antes de eliminar al jefe, reasigna a sus subordinados a otro jefe. Por ejemplo, si quieres eliminar a Carlos Gómez (EmpleadoID = 2) y reasignar a sus subordinados al jefe superior (EmpleadoID = 1):

-- Reasignar subordinados a un nuevo jefe
UPDATE Empleados
SET EmpleadoPadreID = 1 -- Nuevo jefe
WHERE EmpleadoPadreID = 2; -- Jefe que será eliminado

-- Eliminar al jefe
DELETE FROM Empleados
WHERE EmpleadoID = 2; -- Jefe a eliminar

Opción 2: Dejar a los subordinados sin jefe

Si prefieres dejar a los subordinados sin jefe (EmpleadoPadreID = NULL), actualiza primero sus referencias:

-- Dejar subordinados sin jefe
UPDATE Empleados
SET EmpleadoPadreID = NULL
WHERE EmpleadoPadreID = 2; -- Jefe que será eliminado

-- Eliminar al jefe
DELETE FROM Empleados
WHERE EmpleadoID = 2; -- Jefe a eliminar

Caso 2: Eliminar un empleado sin subordinados

Si el empleado no tiene subordinados, simplemente elimina el registro:

DELETE FROM Empleados
WHERE EmpleadoID = 6; -- Empleado a eliminar

Caso 3: Eliminar un empleado con subordinados

Si el empleado que deseas eliminar tiene subordinados, primero debes decidir cómo manejar la relación con sus subordinados:

Opción 1: Reasignar subordinados al jefe del empleado

Reasigna los subordinados al jefe del empleado que será eliminado. Por ejemplo, si eliminas a Pedro López (EmpleadoID = 4) y sus subordinados deben reportar a su jefe (EmpleadoPadreID = 2):

-- Reasignar subordinados al jefe del empleado
UPDATE Empleados
SET EmpleadoPadreID = (SELECT EmpleadoPadreID FROM Empleados WHERE EmpleadoID = 4)
WHERE EmpleadoPadreID = 4;

-- Eliminar al empleado
DELETE FROM Empleados
WHERE EmpleadoID = 4; -- Empleado a eliminar

Opción 2: Dejar subordinados sin jefe

Si prefieres dejar a los subordinados sin jefe (EmpleadoPadreID = NULL):

-- Dejar subordinados sin jefe
UPDATE Empleados
SET EmpleadoPadreID = NULL
WHERE EmpleadoPadreID = 4; -- Empleado a eliminar

-- Eliminar al empleado
DELETE FROM Empleados
WHERE EmpleadoID = 4; -- Empleado a eliminar

Validación previa

Antes de eliminar un jefe o empleado, verifica las relaciones jerárquicas para evitar inconsistencias:

Verificar subordinados

SELECT *
FROM Empleados
WHERE EmpleadoPadreID = 4; -- Cambia por el ID del empleado o jefe a verificar

Validación posterior

Después de realizar el cambio, verifica los resultados:

-- Verificar jerarquía actualizada
SELECT 
    EmpleadoID,
    Nombre,
    Puesto,
    EmpleadoPadreID
FROM Empleados
ORDER BY EmpleadoID;

Nota importante

Si deseas implementar restricciones adicionales, como evitar eliminar un empleado con subordinados sin antes reasignarlos, puedes usar triggers o lógica en la aplicación.

Eliminar un jefe y un empleado en una estructura jerárquica como la tabla Empleados requiere manejar con cuidado las relaciones de dependencia. A continuación te explico cómo hacerlo, asegurando que no haya referencias huérfanas y manteniendo la integridad de la jerarquía.

Escenario: Eliminar un jefe y un empleado

Supongamos que queremos eliminar a un empleado y su jefe directo. El proceso se divide en dos pasos:

1. Eliminar al empleado:

   • Si el empleado tiene subordinados, primero debes decidir qué hacer con ellos. Puede que necesites reasignar a esos subordinados a otro jefe antes de eliminar al empleado.

2. Eliminar al jefe:

   • Si el jefe también tiene subordinados, el proceso es similar: asigna los subordinados a otro jefe antes de eliminarlo.

Caso 1: Eliminar un empleado específico

Primero, eliminemos al empleado con EmpleadoID = 6. Si el empleado tiene subordinados, necesitarás reasignarlos.

Eliminar al empleado (sin subordinados)

Si el empleado no tiene subordinados, puedes eliminarlo directamente:

DELETE FROM Empleados
WHERE EmpleadoID = 6;

Eliminar al empleado (con subordinados)

Si el empleado tiene subordinados, primero debes reasignarlos a otro jefe. Supongamos que el jefe del empleado con EmpleadoID = 6 es EmpleadoID = 4 (es decir, Pedro López), y quieres reasignar a los subordinados del empleado eliminado a otro jefe.

Primero, reasigna a los subordinados:

UPDATE Empleados
SET EmpleadoPadreID = 4  -- Reasignar a otro jefe
WHERE EmpleadoPadreID = 6; -- Subordinados de Jorge Ramírez

Luego, elimina al empleado:

DELETE FROM Empleados
WHERE EmpleadoID = 6; -- Eliminar a Jorge Ramírez

Caso 2: Eliminar al jefe

Ahora, si también quieres eliminar al jefe (EmpleadoID = 4, es decir, Pedro López), debes seguir el mismo proceso: primero reasignar a sus subordinados y luego eliminarlo.

Reasignar subordinados

Antes de eliminar al jefe, reasignamos a sus subordinados a otro jefe. Por ejemplo, si el nuevo jefe será Carlos Gómez (EmpleadoID = 2):

UPDATE Empleados
SET EmpleadoPadreID = 2  -- Reasignar subordinados a Carlos Gómez
WHERE EmpleadoPadreID = 4; -- Subordinados de Pedro López

Eliminar al jefe

Una vez reasignados los subordinados, puedes proceder a eliminar al jefe:

DELETE FROM Empleados
WHERE EmpleadoID = 4; -- Eliminar a Pedro López

Escenario de eliminación en cascada:

Si deseas automatizar la reasignación de subordinados o evitar problemas de integridad referencial, puedes usar una restricción de clave foránea con eliminación en cascada. Esto eliminaría automáticamente a todos los subordinados de un jefe si se elimina el jefe.

Por ejemplo, si al eliminar un jefe también deseas que sus subordinados sean eliminados, puedes definir la clave foránea con la opción ON DELETE CASCADE:

ALTER TABLE Empleados
ADD CONSTRAINT FK_EmpleadoPadre
FOREIGN KEY (EmpleadoPadreID) REFERENCES Empleados(EmpleadoID)
ON DELETE CASCADE;

De esta forma, cuando elimines un jefe, todos sus subordinados serán eliminados automáticamente.

Consideraciones adicionales

1. Integridad referencial:

   • Asegúrate de que no haya empleados que queden huérfanos (sin jefe asignado) sin que sea intencional.

2. Jerarquía circular:

   • Antes de realizar cambios, asegúrate de que no estás creando un ciclo jerárquico, donde un empleado es asignado como jefe de alguien más abajo en la jerarquía.

3. Subordinados sin jefe:

• Si un jefe superior es eliminado, asegúrate de reasignar a sus subordinados a otros jefes antes de borrar al jefe para evitar registros huérfanos.

PROPUESTA PARA VER LA RUTA JERARQUICA DEL EMPLEADOI

USE Basedatos;
GO

-- Verificar si el procedimiento almacenado existe y modificarlo si es necesario
IF EXISTS (
    SELECT 1
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'juridico.seleccionEmpleado') AND type = 'P'
)
BEGIN
    PRINT 'El procedimiento almacenado "juridico.seleccionEmpleado" existe.';
END
ELSE
BEGIN
    EXEC('
    CREATE PROCEDURE juridico.seleccionEmpleado (
        @empleado INT
    )
    AS
    BEGIN
        SET NOCOUNT ON;
        PRINT ''Procedimiento en desarrollo.'';
    END');
END;
GO

-- Modificar o crear el procedimiento almacenado
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO

ALTER PROCEDURE juridico.seleccionEmpleado
    @empleado INT
AS 
BEGIN
    DECLARE @FechaInicioEjecucion DATETIME,
            @Parametros NVARCHAR(MAX);

    SET @FechaInicioEjecucion = GETDATE();
    SET @Parametros = '@empleado:' + CAST(@empleado AS NVARCHAR(10));

    SET NOCOUNT ON;

    BEGIN TRY  
        BEGIN TRANSACTION;

        -- Construir la jerarquía
        WITH jerarquia_max AS (
            SELECT
                EmpleadoID,
                1 AS nivel,
                CAST(CAST(EmpleadoID AS NVARCHAR(MAX)) + '-' + Nombre + '-' + 
                     ISNULL(CAST(EmpleadoPadreID AS NVARCHAR(MAX)), 'NULL')) AS RutaJerarquica
            FROM Empleados
            WHERE EmpleadoPadreID IS NULL

            UNION ALL

            SELECT
                E.EmpleadoID,
                J.nivel + 1 AS nivel,
                CAST(J.RutaJerarquica + '-' + CAST(E.EmpleadoID AS NVARCHAR(MAX)) + '-' + 
                     E.Nombre + '-' + 
                     ISNULL(CAST(E.EmpleadoPadreID AS NVARCHAR(MAX)), 'NULL')) AS RutaJerarquica
            FROM Empleados E
            INNER JOIN jerarquia_max J ON E.EmpleadoPadreID = J.EmpleadoID
        )
        SELECT 
            EmpleadoID,
            nivel,
            RutaJerarquica
        INTO #TempRutaJerarquica
        FROM jerarquia_max
        WHERE EmpleadoID = @empleado;

        -- Descomponer la ruta jerárquica
        SELECT 
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum,
            LTRIM(RTRIM(value)) AS NivelRuta
        INTO #Descompuesta
        FROM #TempRutaJerarquica
        CROSS APPLY STRING_SPLIT(RutaJerarquica, '-');

        -- Transformar filas en columnas
        SELECT 
            MAX(CASE WHEN RowNum % 4 = 1 THEN NivelRuta END) AS EmpleadoID,
            MAX(CASE WHEN RowNum % 4 = 2 THEN NivelRuta END) AS Nombre,
            MAX(CASE WHEN RowNum % 4 = 3 THEN NivelRuta END) AS EmpleadoPadreID,
            MAX(CASE WHEN RowNum % 4 = 0 THEN NivelRuta END) AS NivelId
        INTO #Transpuesta
        FROM #Descompuesta
        GROUP BY (RowNum - 1) / 4;

        -- Consultar los resultados
        SELECT * FROM #Transpuesta;

        -- Log de ejecución
        UPDATE log.logUAJT
        SET FechaFinEjecucion = GETDATE(),
            TiempoEjecucion = DATEDIFF(MS, @FechaInicioEjecucion, GETDATE()),
            Parametros = CONCAT(@Parametros, ', @empleado:', CAST(@empleado AS NVARCHAR(10)))
        WHERE IdLog = SCOPE_IDENTITY();

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION;

        -- Manejo de errores
        UPDATE log.logUAJT
        SET FechaFinEjecucion = GETDATE(),
            TiempoEjecucion = DATEDIFF(MS, @FechaInicioEjecucion, GETDATE()),
            Parametros = CONCAT(@Parametros, ', Error:', ERROR_MESSAGE())
        WHERE IdLog = SCOPE_IDENTITY();

        THROW;
    END CATCH;

    -- Limpiar tablas temporales
    IF OBJECT_ID('tempdb..#TempRutaJerarquica') IS NOT NULL DROP TABLE #TempRutaJerarquica;
    IF OBJECT_ID('tempdb..#Descompuesta') IS NOT NULL DROP TABLE #Descompuesta;
    IF OBJECT_ID('tempdb..#Transpuesta') IS NOT NULL DROP TABLE #Transpuesta;
END;
GO

-- Ejecución de pruebas
-- EXEC juridico.seleccionEmpleado 1;