SQL Server Performance

SP Recursivo

Discussion in 'Preguntas sobre SQL Server en Español.' started by zemog76, Mar 18, 2005.

  1. zemog76 New Member

    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
  2. c_maldon Member

    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
  3. Luis Martin Moderator

    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.



  4. c_maldon Member

    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
  5. zemog76 New Member

    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
  6. c_maldon Member

    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
  7. zemog76 New Member

    Muchas gracias.

    Con un anidamiento de 32 recursiones creo que mi problema se puede resolver.

    Nuevamente, muchas gracias.


    Guillermo

Share This Page