post icon

Realizar Consultas Recursivas en SQL Server

Como todo buen diseñador de base de datos sabe, es bastante común encontrarse con entidades recursivas en el diseño de nuestra BD, 2 ejemplos típicos son el jefe y el subordinado, en el diseño ambas personas se encuentran registradas como tuplas dentro de la entidad Persona o Funcionario (según el diseño que hemos tomado, incluso estaría mejor diseñado si se lo hace en base al cargo), al no existir 2 entidades que tengan cardinalidad 1:M, por que así obtendríamos duplicación de datos, debemos determinar un modo que ambos estén en la misma entidad y a su vez tener la capacidad de controlar quién es jefe de quién, esto se lograría agregando una columna más que sea del mismo dominio que su propia PK, es decir, la columna nueva sería FK de la PK que le determina, logrando así una cardinalidad 1:M recursiva.

Otro ejemplo típico es el caso de los contratos, estos suelen tener la característica que vencen en una fecha determinada, por cuestiones de ventas/marketing al cliente se le facilita normalmente este proceso con una renovación de contrato (en algunos casos automáticas), entonces el nuevo contrato debe poder determinarse lo siguiente: la renovación de que contrato está siendo, o si es el primer contrato, para realizarlo se aplica el mismo ejemplo anterior, una columna FK que sea del mismo dominio de la PK que le determina.

Pues llevemos esto a la práctica, aquí les dejo un script que genera una tabla, e inserta datos en ella, también verán que la sentencia WITH que es la que usaremos para hacer de manera sencilla la consulta recursiva. Tomaremos el ejemplo más típico de los jefes y subordinados 😀

Muchos que ya tienen experiencia en este campo me dirán, ¿y por qué no usas simplemente un cursor para lograrlo?, la respuesta es sencilla, el motor ejecuta de manera más eficiente y con menos consumo de recursos del servidor que usando dicho cursor. Las tablas en este ejemplo no están completamente normalizadas como se darán cuenta, ya que nuestro objetivo no es ese 😛

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
-- Estructura de la tabla recursiva (el campo cargo lo deje en varchar sin FK
-- para que sea mas sencilla la comprensión y corto el ejemplo)
 
-- aquí deberíamos hacer uso de alguna de nuestras base de datos
CREATE TABLE Empleado (NroEmpleado int,
	Nombre varchar(25),
	Apellido varchar(25),
	Cargo varchar(25),
	NroJefe int,
	CONSTRAINT PK_EMPLEADO PRIMARY KEY (NroEmpleado),
	CONSTRAINT FK_JEFE FOREIGN KEY (NroJefe) REFERENCES Empleado)
 
-- Datos de Ejemplo con instruccion INSERT
INSERT INTO Empleado VALUES (1, 'Carlos', 'Mendoza', 'Presidente', NULL)
INSERT INTO Empleado VALUES (2, 'Marta', 'Prieto', 'Vice Presidente', 1)
INSERT INTO Empleado VALUES (3, 'Victor', 'Mongelos', 'Gerente General', 1)
INSERT INTO Empleado VALUES (4, 'Betty', 'Martinez', 'Gerente RRHH', 3)
INSERT INTO Empleado VALUES (5, 'Marcos', 'Arce', 'Gerente Informatico', 3)
INSERT INTO Empleado VALUES (6, 'Hugo', 'Zorrilla', 'Gerente de Ventas', 3)
INSERT INTO Empleado VALUES (7, 'Marcela', 'Britoz', 'Gerente Comercial', 3)
INSERT INTO Empleado VALUES (8, 'Carlota', 'Muñoz', 'Gerente de Operaciones', 3)
INSERT INTO Empleado VALUES (9, 'Ricardo', 'Gallas', 'Atención al Cliente', 4)
INSERT INTO Empleado VALUES (10, 'Mafalda', 'Quiñonez', 'Auxiliar Contable', 7)
INSERT INTO Empleado VALUES (11, 'Cristian', 'Leite', 'Telemaketer', 4)
INSERT INTO Empleado VALUES (12, 'Osvaldo', 'Acuña', 'Team Leader', 8)
INSERT INTO Empleado VALUES (13, 'Consuelo', 'Vargas', 'Supervisor', 6)
INSERT INTO Empleado VALUES (14, 'Mateo', 'Dusrte', 'Supervisor de Seguridad', 4)
INSERT INTO Empleado VALUES (15, 'Miguel', 'Gllvan', 'Guardia de Seguridad', 14)
INSERT INTO Empleado VALUES (16, 'Carlos', 'Troche', 'Vendedor', 6)
INSERT INTO Empleado VALUES (17, 'Martina', 'Vera', 'Vendedor', 6)
INSERT INTO Empleado VALUES (18, 'Jose', 'Troche', 'Vendedor', 6)
INSERT INTO Empleado VALUES (19, 'Bruno', 'Zarza', 'Jefe de Marketing', 7)
INSERT INTO Empleado VALUES (20, 'Ana', 'Carreras', 'Auxiliar Contable', 7)
 
GO
--Consulta Recursiva para conocer los subordinados de un empleado en el organigrama hasta el mas bajo en la piramide
 
WITH EmpCTE (NroEmpleado, Nombre, Apellido, Cargo, NroJefe)
AS
(
	SELECT NroEmpleado, Nombre, Apellido, Cargo, NroJefe
		FROM Empleado
		WHERE NroEmpleado = 4
	UNION ALL
--RECURSIVIDAD
	SELECT e.NroEmpleado, e.Nombre, e.Apellido, e.Cargo, e.NroJefe
		FROM Empleado AS e JOIN EmpCTE AS m
			ON e.NroJefe = m.NroEmpleado
)
-- Desplegar ResultSet
SELECT * FROM EmpCTE
 
--Consulta Recursiva para conocer todos los jefes de un subordinado, hasta el jefe más alto en el organigrama
GO
WITH EmpCTE (NroEmpleado, Nombre, Apellido, Cargo, NroJefe)
AS
(
	SELECT NroEmpleado, Nombre, Apellido, Cargo, NroJefe
		FROM Empleado
		WHERE NroEmpleado = 14
	UNION ALL
--RECURSIVIDAD
	SELECT e.NroEmpleado, e.Nombre, e.Apellido, e.Cargo, e.NroJefe
		FROM Empleado AS e JOIN EmpCTE AS m
			ON e.NroEmpleado = m.NroJefe
)
-- Desplegar ResultSet
SELECT * FROM EmpCTE

Comentarios desde Facebook:

  1. avatar
    benigno MEXICO Google Chrome Windows
    2 Diciembre 2013 at 23:55 #

    Hola, muchas gracias por la información. Si digamos tuviera a cada empleado una dirección y quisiera saber cual el el jefe de cada dirección como podria hacerle?

    Saludos muchas gracias

  2. avatar
    Oscar R. Onorato Internet Explorer Windows
    24 Noviembre 2012 at 16:07 #

    Hola, gracias por el ejemplo. Aunque no llego a ver en la imagen algunas cosas. ¿Cómo hago para ver el archivo .sql con el código del ejemplo?

    Desde ya muchas gracias.

    • avatar
      Oscar R. Onorato Internet Explorer Windows
      24 Noviembre 2012 at 16:10 #

      Perdón,

      No había visto que estaba perfectamente publicada la imagen con el cógido.

      ¡Muchas gracias!

  3. avatar
    Jhony COLOMBIA Google Chrome Windows
    26 Octubre 2012 at 11:14 #

    quisiera saber como puedo usar una función recursiva y dentro de esta ejecutar procedimientos almacenados en base a la tabla que tiene la recursividad

  4. avatar
    DjMiki Google Chrome Windows
    2 Marzo 2011 at 14:56 #

    Hola que tal GeekZero bueno chekee tu ejemplo y bueno lo había visto en una forma mas complicada en el Technet, pero sabes trato de hacer eso pero no me funciona siempre me sale que la consulta a sobrepasado el limite, ahora otra pregunta seria, en vez de mostrar el id del jefe, por q no mostrar el nombre del jefe, y si se puede como seria?

    • avatar
      GeekZero PARAGUAY Google Chrome Windows
      2 Marzo 2011 at 16:00 #

      Si te da ese error es porque has puesto una sentencia where que no produce un paro. revisalo.

      Con respecto de mostrar el nombre del jefe puedes hacerlo asi:

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      
      WITH EmpCTE (NroEmpleado, Nombre, Apellido, Cargo, NroJefe, Jefe)
      AS
      (
      	SELECT e.NroEmpleado, e.Nombre, e.Apellido, e.Cargo, e.NroJefe, (select Nombre from empleado where NroEmpleado = e.NroJefe)
      		FROM Empleado e
      		WHERE NroEmpleado = 14
      	UNION ALL
      --RECURSIVIDAD
      	SELECT e.NroEmpleado, e.Nombre, e.Apellido, e.Cargo, e.NroJefe, (select Nombre from empleado where NroEmpleado = e.NroJefe)
      		FROM Empleado AS e JOIN EmpCTE AS m
      			ON e.NroEmpleado = m.NroJefe
      			--and mNroEmpleado = e.NroJefe
      )
      -- Desplegar ResultSet
      SELECT * FROM EmpCTE
      • avatar
        DjMiki Google Chrome Windows
        2 Marzo 2011 at 16:43 #

        Muchas Gracias geekZero, me sirvió, pero sabes probe el codigo funciono bien, pero como quizás no ubiese querido relizar algo asi como recursivas, probe esto.
        SELECT C.nomCat,C.descCat,(SELECT nomCat FROM Categorias where idCat=C.catSup) FROM Categorias C

        Y eso me dio el mismo resultado, que la estructura de la consulta recursiva, pero mi pregunta es; Es igual?, genera algún retrazo en el motor de BD y por consecuencia cual debería ser el ideal..

        • avatar
          GeekZero PARAGUAY Google Chrome Windows
          2 Marzo 2011 at 16:52 #

          Eso que has escrito no es recursividad, lo único que hace es mostrarte quien es su categoria padre, pero no genera un arbol.

          En el query recursivo podrás notar que si seleccionas al gerente verás todos los que estan debajo de su cargo, y quien esta debajo de su primer subordinado, quien es el subordinado de ese subordinado. Si eliges por ejemplo el Gte de Ventas verás abajo al Jefe de Ventas, luego al Sub-Jefe de Ventas luego a los vendedores por así decirlo. Y nunca verás al jefe de informatica por ejemplo. En cambio con el query que pusiste si verás a todos o si filtras verás menos gente pero no en escala jerarquica. Se comprende la diferencia?

          Si el query que has puesto satisfizo tu necesidad, no estabas buscando recursividad.

          Obviamente existen un par de técnicas más para crear recursividad, pero ésta es la más eficiente sobre SQL Server ya que está diseñado y optimizado para esa tarea.

          Saludos.

  5. avatar
    Alejandro ECUADOR Internet Explorer Windows
    29 Marzo 2010 at 12:47 #

    se puede delimitar la consulta?? o sea poner que llegue solo hasta el segundo o tercer nivel x ejemplo??

    • avatar
      GeekZero Google Chrome Windows
      31 Marzo 2010 at 21:01 #

      Para la sintaxis "with" no lo he hecho nunca, pero podrías probar con un cursor que maneje cuantos niveles esta entrando, o algún tipo de campo que determine niveles (si es que esto lo tienes implementado, pero no es muy práctico y común)

    • avatar
      GeekZero PARAGUAY Google Chrome Windows
      2 Marzo 2011 at 21:16 #

      Ya ha pasado mucho tiempo a esto, pero la solución es más que sencilla. No hace falta que modifiques la consulta recursiva, tan solo al

      1
      2
      
      -- Desplegar ResultSet
      SELECT * FROM EmpCTE

      que se encuentra al final del with debes agregarle una clausula top, ej:

      1
      2
      
      -- Desplegar ResultSet
      SELECT top 2 * FROM EmpCTE

Responder