Hace poco estaba preparando una platica de Sql Server 2008 y me preguntaron que eran Common Table Expressions (CTEs), una característica recien introducida en Sql Server 2005. En su momento lo vi, pero no había tenido la necesidad de usarlos hasta ahora. Estos sirven para hacer queries sobre esquemas jerarquicos. Veamos este ejemplo real. Nosotros tenemos una lista de Nodos que representan categorías y pueden tener subcategorías y sub-subcategorias con N niveles de profundidad.
Si supieramos que sólo vamos a mezclar marcas con categorías y estas últimas con líneas, podríamos resolver todo en un par de joins, el problema viene cuando el nivel de profundidad es "ilimitado".
Este es el código con CTE en Sql Server 2005.
WITH TreeView(NodeId, Name, Description, Level, Father, Selectable, Enabled)--Definimos nuestro CTE
AS
(
-- Definimos la raíz o miembro anclar
SELECT NodeId, Name, Description, 0 as Level, Father, Selectable, Enabled
FROM Nodes
WHERE NodeId = @NodeId
UNION ALL
-- Definimos el miembro recursivo haciendo referencia a nuestro CTE
SELECT n.NodeId, n.Name, n.Description, tv.Level+1 as Level, n.Father, n.Selectable, n.Enabled
FROM Nodes n
INNER JOIN TreeView tv on n.Father = tv.NodeId
)-- Ahora simplemente consultamos nuestro CTE
SELECT * from TreeView
Order by Level
Al final terminaremos con algo como esto.
Lo más importante a notar es como al definir nuestro miembro recursivo hacemos referencia a TreeView.NodeId para poder encontrar la relación padre-hijo. Esa es la clave de todo.
Como funciona, se obtiene el miembro ancla y se une por cada uno de estos el resultado de ejecutar el query para los miembros recursivos, una vez que este último no regresa filas en automático termina la recursión para continuar con una siguiente fila así hasta acabar con todos.
Lamentablemente en este proyecto aún estamos usando Sql Server 2000, así que esto no sirvio más que para que me diera coraje seguir batalalndo con Cursores, aquí esta la solución que ya tenía para 2000, no la voy a describir mucho, sólo dire que nos obliga a tener un procedure, cursores y tablas temporales con la limitante de sólo 32 niveles de anidación (por las llamadas a Sprocs):
ALTER procedure [dbo].[GetChildsForNode]
@father integer,
@level integer
as
begin
/*
* Obtiene de manera recursiva todos los nodos hijos y los guarda en una tabla temporal #nodes
*
* Autor: Miguel Madero
* Fecha de creación: 02/05/2008
* Fecha de última modificación: 02/05/2008
*
*/
-- Insert current level nodes
insert into #nodes
select * from Nodes
where father = @father and [level] = @level and [Enabled] = 1-- Each insertedNode will now be the father
declare @newFather integer
declare @newLevel integer
-- We wanna go down 1 level
set @newLevel = @level + 1
declare cursorNodesByLevel cursor for
select Father from #nodes where [level] = @level and [Enabled] = 1
open cursorNodesByLevel
-- Each insertedNode will now be the father
Fetch Next from cursorNodesByLevel into @newFather
WHILE (@@FETCH_STATUS<>-1)
BEGIN
IF(@@FETCH_STATUS<>-2)
BEGIN
-- We call ourselves with the new father and newLevel
exec GetChildsForNode @newFather, @newLevel
END
-- Each insertedNode will now be the father
Fetch Next from cursorNodesByLevel into @newFather
END
-- Cleanup
Close cursorNodesByLevel
Deallocate cursorNodesByLevel
end
Lo usarías de la siguiente forma:
CREATE TABLE #nodes
(
NodeId int NOT NULL,
[Name] varchar(50) NOT NULL,
[Description] varchar(500) NULL,
[Level] int NOT NULL,
Father int NULL,
Selectable bit NOT NULL,
[Enabled] bit NOT NULL
)
-- We fill the ChildNodes which will be left in the temp table
exec GetNodeWithChilds @nodeId
-- Now we do something with that like get the products on any node
select p.ProductId, p.NodeId, ad.descr, ad.precioUsuario, ad.disponiblesUsuario
from Products p
join ProductsAspel pa on p.ProductId = pa.ProductId
join ArticulosDisponibles ad on ad.clv_art = pa.AspelKey
where usId = @usId and NodeId in (Select NodeId from #nodes)
order by descr, clv_art,marcnombre, desc_lin
-- Cleanup
Drop Table #nodes
Por cierto Sql Server 2008 tiene un nuevo Hierarchy Type del que les platicaremos en los eventos de presentación del producto que estaremos haciendo por todo México.
Entradas
Suscribete