Recursividad con Sql Server

Una función muy interesante de Sql Server es la de poder seleccionar un conjunto de datos de forma recursiva de manera que podemos obtener una serie en estructura de arbol.

Partimos de una tabla que tiene dos campos, llamados clave y padre, el campo clave se relaciona con el padre para formar la estructura en arbol.

El siguiente procedimiento almacenado muestra un ejemplo de como conseguir esto:

ALTER PROCEDURE [dbo].[Usuarios_seguridad_seleccionar]
AS
BEGIN    
    DECLARE @minClave int
    SELECT @minClave = MIN(Clave) FROM dbo.Usuarios_seguridad;
    
    WITH UsuariosAccesos AS
    (
        SELECT top 1 us1.Padre,us1.Clave,us1.Variable,us1.Modulo,us1.Contenido,us1.Acceso,us1.Imagen 
        FROM dbo.Usuarios_seguridad us1 
        WHERE us1.Clave = @minClave
        UNION ALL
        SELECT top 100 percent us2.Padre,us2.Clave,us2.Variable,us2.Modulo,us2.Contenido,us2.Acceso,us2.Imagen 
        FROM dbo.Usuarios_seguridad us2 
        INNER JOIN UsuariosAccesos AS us3 ON us3.Clave = us2.Padre  
        WHERE us2.Clave <> @minClave 
    )
 
    SELECT TOP 100 PERCENT ia.Padre,ia.Clave,ia.Variable,ia.Modulo,ia.Contenido,ia.Acceso,ia.Imagen 
    FROM UsuariosAccesos ia
    ORDER BY padre, clave
END
GO
 
La clausula with debe contener un miembro delimitador, en este caso el formado por la  primera sentencia Sql que hace referencia al valor mínimo (Primer nodo) y el segundo miembro recursivo que hace referencia a la misma tabla definida el la clausula With.
El procedimiento almacenado calcula el valor mínimo del nodo con la clave mas baja, posteriormente va leyendo cada nodo relacionado de forma recursiva ya que en el inner join se relaciona con el conjunto de datos definido en la clausula WiTH, finalmente devuelve el conjunto de datos en un orden determinado, el resultado obtenido es el siguiente:

image

La consulta retorna los datos de forma similar a la estructura de arbol que posteriormente se carga en el tree.

image

Para cargar los datos en el control, podiamos haberlo hecho sin recurrir a la recursividad en Sql Server y hacerlo directamente con el lenguaje de programación en el cliente, pero hay veces que puede ser mas interesante recurrir al servidor en lugar de hacerlo en el cliente, por ejemplo para buscar un dato determinado aprovechando las ventajas de las busquedas en el servidor y devolver su nodo, borrar todos los nodos relacionados o simplemente por descargar la tarea del lado del cliente.

Si quereis mas información sobre la clausula WITH que permite realizar este tipo de consultas podeis encontrala en http://msdn.microsoft.com/en-us/library/ms175972.aspx (Ingles) o http://technet.microsoft.com/es-es/library/ms175972(SQL.90).aspx (Español).

4 comentarios en “Recursividad con Sql Server”

  1. El tree que aparece en la foto tiene mas de 1200 lineas de código, ya que incorpora drag & drop y otras funcionalidades que aparecen en el menú de la derecha, poner todo el código en el blog no tiene mucho sentido, ya que esta totalmente adaptado a la funcionalidad que hemos implementado, utiliza procedimientos almacenados y otras funciones que crean dependencia con el entorno de datos, si quieres avanzar en estas funciones puedes acceder a ejemplos sencillos con poco código, que te permitiran crear nuevos nodos, borrarlos, realizar drag & drop e implementar otras funciones, en http://www.codeproject.com/

    Espero que te sirva. Saludos.

  2. Solo quisiera agregar un comentario, este ejemplo solo devuelve los “hijos” de un solo nodo, en mi caso, todos mis nodos de primer nivel tienen el padre 0, entonces, si quiero traer el árbol completo, tengo que cambiar el where por us1.Clave in (SELECT Clave FROM … WHERE Padre = 0) y el otro us2.Clave not in (SELECT Clave FROM … WHERE Padre = 0) y asi trae todo el arbol completo, un poco desordenado pero ordenarlo es sencillo.

    Saludos!!

  3. @Omar, te equivocas, el ejemplo anterior devuelve los hijos de todos los nodos, es recursivo, pero todos los arboles tienen un nodo de partida este es el primer elemento en el puede colgar uno o varios hijos, pero siempre hay uno del que cuelgan los demas, si creas una pequeñas estructura como la anterior, podrás comprobarlo.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *