SP Recursivo | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SP Recursivo

Hola a todos, les comento mi situación:
Tengo una tabla que contiene información de Puntos Consecutivos, por ejemplo,
Punto Inicial Punto Final
1 3
3 5 Significa que el punto 1 es anterior inmediato al 3, el 3 es anterior inmediato al 5. Necesito generar los datos de otra tabla llamada Tramos. Según la tabla PuntosConsecutivos mostrada anteriormente, Tramos debería contener Extremo Inicial Extremo Final
1 3
1 5
3 5 Significa que desde el punto 1 puedo llegar al 3 y al 5, y desde el 3 puedo llegar al 5. Mi idea era armar un procedimiento almacenado recursivo que recorra la tabla PuntosConsecutivos y me vaya generando los registros de Tramos. La recursividad es para hacer más simple la programación del procedimiento. El problema con el que me encontré es que no funciona, puede ser porque haya progrmado mal o porque SQL Server no se banca el tipo de recursión que necesito.
Le paso lo que implementé, en una de esas me pueden ayudar.
CREATE PROCEDURE [dbo].[InsertarTramosDemorasEntrePuntosCobro]
/* Punto de Cobro Inicial del Tramo. */
@PuntoCobroInicialTramo TinyInt,
/* Punto de Cobro a para buscar un consecutivo. El consecutivo de éste formará tramo con @PuntoCobroInicialTramo. */
@PuntoCobroIntermedio TinyInt,
/* Demora en minutos del Tramo. */
@DemoraTramo SmallInt
AS BEGIN
/* Procedimiento recursivo que inserta en la tabla OP_COP.DemorasEntrePuntosCobro los tramos entre Puntos de Cobro
que pueden son consecutivos inmediatos o no inmediatos. */ DECLARE @PCConsecutivo TinyInt
DECLARE @DemoraConsecutivo SmallInt,
@DemoraAcumulada SmallInt /* Obtener los consecutivos a @PuntoCobroIntermedio. */
DECLARE Consecutivos CURSOR FOR
SELECT PuntoCobroFinal, Demora
FROM OP_CAC.dbo.PuntosCobroConsecutivos
WHERE PuntoCobroInicial = @PuntoCobroIntermedio
ORDER BY PuntoCobroFinal
OPEN Consecutivos
FETCH NEXT FROM Consecutivos INTO @PCConsecutivo, @DemoraConsecutivo WHILE (@@FETCH_STATUS = 0) BEGIN
/* Caclular la nueva demora del tramo. */
SET @DemoraAcumulada = @DemoraTramo + @DemoraConsecutivo /* Insertar en DemorasEntrePuntosCobro el registro para el nuevo tramo. */
PRINT ‘ Insert, PIT: ‘ + Cast(@PuntoCobroInicialTramo as VarChar(3)) + ‘, PFT: ‘ + Cast(@PCConsecutivo as VArChar(3)) + ‘, Demora: ‘ + Cast(@DemoraAcumulada as VarChar(3))
INSERT INTO DemorasEntrePuntosCobro
(PuntoCobroInicial, PuntoCobroFinal, MaxTiempo)
VALUES (@PuntoCobroInicialTramo, @PCConsecutivo, @DemoraAcumulada) /* Si ocurrió un error en la inserción, salir. */
IF @@ERROR <> 0 RETURN /* Llamar la recursión para continuar agregando tramos con los consecutivos. */
execute InsertarTramosDemorasEntrePuntosCobro @PuntoCobroInicialTramo, @PCConsecutivo, @DemoraAcumulada /* Ir al próximo registro del cursor, es decir, a otro consecutivo
de @PuntoCobroIntermedio. */
FETCH NEXT FROM Consecutivos INTO @PCConsecutivo, @DemoraConsecutivo
END
/* Liberar el cursor. */
CLOSE Consecutivos
DEALLOCATE Consecutivos END Desde ya muchas gracias. Hasta luego. Guillermo
Te mando este script que segun mis pruebas da
el resultado que necesitas.
Te recomiendo no usar cursores en SQL Server.
Saludos.
—– Create table PuntosCobroConsecutivos
(
PuntoCobroInicial int,
PuntoCobroFinal int,
Demora int
) Create table PuntosCobroConsecutivosFinal
(
PuntoCobroInicial int,
PuntoCobroFinal int,
Demora int
) Insert PuntosCobroConsecutivos values (1,3,40)
Insert PuntosCobroConsecutivos values (3,5,30) Insert PuntosCobroConsecutivosFinal
(
PuntoCobroInicial,
PuntoCobroFinal,
Demora
)
Select PuntoCobroInicial,
PuntoCobroFinal,
Demora
From PuntosCobroConsecutivos while @@rowcount != 0 Begin
Insert PuntosCobroConsecutivosFinal
(
PuntoCobroInicial,
PuntoCobroFinal,
Demora
)
Select c.PuntoCobroInicial ,
f.PuntoCobroFinal,
f.demora + c.demora
From PuntosCobroConsecutivos c,
PuntosCobroConsecutivosFinal f
Where c.PuntoCobroFinal = f.PuntoCobroInicial
And Not Exists (
Select *
From PuntosCobroConsecutivosFinal f2
Where c.PuntoCobroInicial =f2.PuntoCobroInicial
And f.PuntoCobroFinal =f2.PuntoCobroFinal
)
End — ver resultado Select *
From PuntosCobroConsecutivosFinal
Gracias Malcon, tu participaciòn y tus conocimientos de programación es el complemento ideal que le faltaba a este forum. Desde ya esa no es mi área, y antes de tu colaboración tenía que traducir el post y pasarlo al Forum de Desarrollo. Luis Martin
Moderator
SQL-Server-Performance.com One of the symptoms of an approaching nervous breakdown is the belief that one’s work is terribly important
Bertrand Russell
All postings are provided “AS IS” with no warranties for accuracy.
Es un placer colaborar con ustedes. El foro no solo es un sitio de conocimiento sino de apoyo y contensión, cuando tengo una duda me siento muy tranquilo si escucho la opinión del foro. La colaboración es mutua, muchas gracias. Zemog76, me quedé con una duda:
Podrías definir caminos ciclicos?
Saludos
Muchas gracias.
No he analizado en detalle la solución que me das, pero es similar a una que me dió un compañero de trabajo. No se permiten caminos cíclicos, al menos eso es lo que sé hasta ahora. Ahora tengo una pregunta: El tipo de recursión que quise implementar, ¿se puede llevar a cabo en MSSQL Server? Yo he utilizado Firebird y allí si se puede hacer este tipo de recursiones. Saludos. Guillermo
Por un lado tenías un problema con el cursor, ya que al usar el procedimiento como recursivo falto declararlo como LOCAL. Modifique la linea DECLARE Consecutivos CURSOR FOR
por
DECLARE Consecutivos CURSOR LOCAL FOR y el procedimiento llenó la tabla DemorasEntrePuntosCobro como vos lo solicitaste. Exec InsertarTramosDemorasEntrePuntosCobro 1,1,0 O sea: A donde se puede llegar desde el punto 1 ?
Extremo Inicial Extremo Final
1 3
1 5 Pero el problema es la limitación de SQL Server para el anidamiento, no se puede superar el limite de 32. Por lo tanto no podrias definir una camino:
Extremo Inicial Extremo Final
1 2 — Ok
2 3 — Ok
3 4 — Ok
4 … — Ok
31 32 — Ok
32 33 — Falla por anidamiento Si no superás esta limitacion podes utilizar procedimientos recursivos. Saludos
Muchas gracias. Con un anidamiento de 32 recursiones creo que mi problema se puede resolver. Nuevamente, muchas gracias.
Guillermo
]]>