Definición de un procedimiento almacenado

Este método que te propongo para devolver un subárbol es bastance eficiente
para SQL Server 2000, no usa cursores ni procesamiento fila a fila como el
que estás usando ahora, tampoco usa recursividad con lo que el número de
niveles no está limitado a 32. Hace un recorrido en anchura del árbol en vez
de un recorrido en profundidad como el que estás usando ahora. Usa una tabla
temporal y joins. Hará tantos joins como niveles tenga que recorrerse.

Aquí está:

CREATE DATABASE TestDB

USE TestDB


CREATE TABLE Proyectos
(
IdProyecto int PRIMARY KEY,
IdProyectoPadre int,
NombreProyecto varchar(50)
)

GO


INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(1, NULL, 'Proyecto 1')
INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(2, 1, 'Proyecto 1.1')
INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(3, 1, 'Proyecto 1.2')
INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(4, 1, 'Proyecto 1.3')

INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(5, 2, 'Proyecto 1.1.1')
INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(6, 2, 'Proyecto 1.1.2')
INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(7, 2, 'Proyecto 1.1.3')

INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(8, 3, 'Proyecto 1.2.1')
INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(9, 3, 'Proyecto 1.2.2')
INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(10, 3, 'Proyecto 1.2.3')

INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(11, 4, 'Proyecto 1.3.1')
INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(12, 4, 'Proyecto 1.3.2')
INSERT INTO Proyectos(IdProyecto, IdProyectoPadre, NombreProyecto) VALUES
(13, 4, 'Proyecto 1.3.3')



GO


CREATE PROCEDURE SubArbolProyectos
@IdProyectoRaiz int
AS
SET NOCOUNT ON
CREATE TABLE #Proyectos(Nivel smallint, IdProyecto int, PRIMARY KEY (Nivel,
IdProyecto))
INSERT INTO #Proyectos(Nivel, IdProyecto) VALUES (0, @IdProyectoRaiz)

DECLARE @Nivel smallint
SET @Nivel = 0

WHILE 1 = 1
BEGIN
INSERT INTO #Proyectos(Nivel, IdProyecto)
SELECT @Nivel + 1, P.IdProyecto
FROM #Proyectos T JOIN Proyectos P ON T.IdProyecto = P.IdProyectoPadre
WHERE T.Nivel = @Nivel
IF @@ROWCOUNT = 0 BREAK
SET @Nivel = @Nivel + 1
END
SELECT *
FROM #Proyectos


GO

select * from proyectos

exec SubArbolProyectos 1

exec SubArbolProyectos 2


Saludos:


Jesús López
www.solidq.com



"Mauricio" escribió en el mensaje
news:

Hola a todos,
siguiendo con mi procedimiento almacenado en una tabla recursiva, definí
el siguiente:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[MostrarArbolProyectos]
(
@Root int
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @ProyectoID int, @ProyectoNombre varchar(150)

SET @ProyectoNombre = (SELECT Nombre FROM dbo.Proyectos WHERE IDProyecto =
@Root)

PRINT REPLICATE('-', @@NESTLEVEL * 4) + @ProyectoNombre

SET @ProyectoID = (SELECT MIN(IDProyecto) FROM dbo.Proyectos WHERE IDPadre
= @Root)

WHILE @ProyectoID IS NOT NULL
BEGIN
EXEC dbo.MostrarArbolProyectos @ProyectoID
SET @ProyectoID = (SELECT MIN(IDProyecto) FROM dbo.Proyectos WHERE IDPadre
= @Root AND IdProyecto > @ProyectoID)
END
END

Este procedimiento imprime una estructura de tipo árbol de acuerdo a la
rama que le pasemos como parámetro y funciona bien. Mi pregunta es: cómo
modifico el sp para que el resultado me lo devuelva como un select?
Espero que me hayan entendido.
Muchas gracias.

Mauricio
Copenhague, Dinamarca

Muchas gracias, Jesús!!!

Mauricio
Copenhague, Dinamarca

Hola nuevamente, Jesús.
Tengo una nueva cuestión, cómo puedo modificar el sp para que me vaya
guardando la ruta para los proyectos? Ejemplo: Proyecto 1.1 cuelga de
Proyecto 1, entonces yo quiero guardar en la tabla temporal algo como:
Proyecto 1\Proyecto 1.1
Muchas gracias desde ya.

Mauricio
Copenhague, Dinamarca

CREATE PROCEDURE [dbo].SubArbolProyectos
@IdProyectoRaiz int
AS
SET NOCOUNT ON
CREATE TABLE #Proyectos(
Nivel smallint,
IdProyecto int,
RutaProyecto varchar(8000),
PRIMARY KEY (Nivel, IdProyecto)
)
INSERT INTO #Proyectos(Nivel, IdProyecto, RutaProyecto)
SELECT 0, @IdProyectoRaiz, NombreProyecto
FROM Proyectos
WHERE IdProyecto = @IdProyectoRaiz

DECLARE @Nivel smallint
SET @Nivel = 0

WHILE 1 = 1
BEGIN
INSERT INTO #Proyectos(Nivel, IdProyecto, RutaProyecto)
SELECT @Nivel + 1, P.IdProyecto, T.RutaProyecto + '\' + P.NombreProyecto
FROM Proyectos P INNER JOIN #Proyectos T ON T.IdProyecto =
P.IdProyectoPadre
WHERE T.Nivel = @Nivel
IF @@ROWCOUNT = 0 BREAK
SET @Nivel = @Nivel + 1
END
SELECT *
FROM #Proyectos






"Mauricio" escribió en el mensaje
news:

Hola nuevamente, Jesús.
Tengo una nueva cuestión, cómo puedo modificar el sp para que me vaya
guardando la ruta para los proyectos? Ejemplo: Proyecto 1.1 cuelga de
Proyecto 1, entonces yo quiero guardar en la tabla temporal algo como:
Proyecto 1\Proyecto 1.1
Muchas gracias desde ya.

Mauricio
Copenhague, Dinamarca

Jesús López a utilisé son clavier pour écrire :

CREATE PROCEDURE [dbo].SubArbolProyectos
@IdProyectoRaiz int
AS
SET NOCOUNT ON
CREATE TABLE #Proyectos(
Nivel smallint,
IdProyecto int,
RutaProyecto varchar(8000),
PRIMARY KEY (Nivel, IdProyecto)
)
INSERT INTO #Proyectos(Nivel, IdProyecto, RutaProyecto)
SELECT 0, @IdProyectoRaiz, NombreProyecto
FROM Proyectos
WHERE IdProyecto = @IdProyectoRaiz

DECLARE @Nivel smallint
SET @Nivel = 0

WHILE 1 = 1
BEGIN
INSERT INTO #Proyectos(Nivel, IdProyecto, RutaProyecto)
SELECT @Nivel + 1, P.IdProyecto, T.RutaProyecto + '\' + P.NombreProyecto
FROM Proyectos P INNER JOIN #Proyectos T ON T.IdProyecto =
P.IdProyectoPadre
WHERE T.Nivel = @Nivel
IF @@ROWCOUNT = 0 BREAK
SET @Nivel = @Nivel + 1
END
SELECT *
FROM #Proyectos






"Mauricio" escribió en el mensaje
news:

Hola nuevamente, Jesús.
Tengo una nueva cuestión, cómo puedo modificar el sp para que me vaya
guardando la ruta para los proyectos? Ejemplo: Proyecto 1.1 cuelga de
Proyecto 1, entonces yo quiero guardar en la tabla temporal algo como:
Proyecto 1\Proyecto 1.1
Muchas gracias desde ya.

Copenhague, Dinamarca

Nuevamente gracias, Jesús!!!!

Mauricio
Copenhague, Dinamarca