Recursive stored procedure

[paulnamroud]

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(destinat ion)

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