Stored procedure recursive

• paulnamroud

  SSC-Addicted

  Points: 412

  More actions

  March 15, 2009 at 10:56 pm

  #129880

  Hi all,

  I need your help.

  I'm creating a program to display the circuit of Bus by city and stop.

  This is my example and i don't know how to do it:

  I have 3 differents circuits of bus and each circuit can have one or multiple transfer to another circuit.

  So let's say i'm looking for a trajet from City1/Stop1 (source) To City3/Stop11(destination)

  Here's the scenario:

  Bus 1:

  ------

  City1/ Stop1: 6h30 am (bus departure) / SOURCE

  City1/ Stop2: 6h50 am -> Transfer to Bus 2 (transfert at 6h55am)

  City1/ Stop3: 6h55 am

  City1/ Stop4: 7h00 am

  ...

  City1/ Stop12: 12h50 pm

  City1/ Stop13: 13h00 pm

  ...

  Bus 2:

  ------

  City2/ Stop1: 6h00 am (bus departure)

  City2/ Stop2: 6h10 am

  City2/ Stop3: 6h20 am

  City1/ Stop2 (e.g stop 4): 6h55 am ***

  City2/ Stop5: 7h00 am

  City2/ Stop6: 7h10 am

  City2/ Stop7: 7h20 am -> Transfer to Bus 3 (at 7h28 am)

  City2/ Stop8: 7h30 am

  City2/ Stop9: 7h40 am -> Transfer to Bus 8

  City2/ Stop10: 7h50 am

  City2/ Stop11: 8h05 am

  City2/ Stop12: 8h15 am

  etc...

  Bus 3:

  ------

  City3/ Stop1: 5h00 am (departure)

  City3/ Stop2: 5h40 am

  City3/ Stop3: 6h20 am

  City3/ Stop3: 6h50 am

  City3/ Stop5: 7h00 am

  City3/ Stop6: 7h20 am

  City3/ Stop7: 7h28 am ***

  City3/ Stop8: 7h35 am

  City3/ Stop9: 7h40 am

  City3/ Stop10: 7h45 am

  City3/ Stop11: 7h55 am / DESTINATION

  City3/ Stop12: 8h15 am

  etc...

  So my trajet should be:

  -----------------------

  Bus1:

  Departure: City1/Stop1: 6h00 am

  Arrival: City1/Stop2: 6h50 am

  Transfert to Bus 2:

  Departure: City1/ Stop2 (e.g stop 4): 6h55 am

  Arrival: City2/ Stop7: 7h20 am

  Transfert to Bus 3:

  Departure: City3/ Stop7: 7h28 am

  Arrival: City3/ Stop11: 7h55 am

  Can anyone help me and tell me how to do it as a recursive stored procedure ?

  I tried many examples/algorithms but i didn't succeed.

  I have 2 differents tables.

  The first table is dedicated for Circuits/Bus:

  CREATE TABLE [dbo].[Trajets](

  [TRAJET_ID] [int] IDENTITY(1,1) NOT NULL,

  [CIRCUIT_ID] [int] NOT NULL,

  [CITY_ID] [int] NOT NULL,

  [STOP_ID] [int] NOT NULL,

  [DAY_ID] [int] NOT NULL,

  [HOUR] [int] NOT NULL,

  [MINUTE] [int] NOT NULL,

  [TYPE_ARRET] [nvarchar](50) NULL,

  [TRANSFERT] [bit] NOT NULL,

  [TYPE_TRANSFERT] [int] NULL,

  [DIRECTION] [nvarchar](50) NULL,

  CONSTRAINT [PK_Trajets] PRIMARY KEY (TRAJET_ID)

  )

  The 2nd one is dedicated for Transfers:

  CREATE TABLE [dbo].[Transferts](

  [TRANSFERT_ID] [int] IDENTITY(1,1) NOT NULL,

  [TRAJET_ID] [int] NOT NULL,

  [CIRCUIT_ID] [int] NOT NULL,

  CONSTRAINT [PK_Transferts] PRIMARY KEY (Transfert_Id)

  )

  Thank you

  Paul