need help in sql scripts

[shooter2749]

so this script check for every journey from one town to another and works perfectly when there is only the first half of the database table, but the teacher asked us to add every journey but in the inverse sens and now when I execute it sql stops it at 100 tries since its looping and wont stop by itself.  we now have to fix the looping issue as a homework but me and the other guys tried alot of things and nothing fixed the issue.any sql masters to help us out? 

ALTER PROCEDURE [dbo].[trajets]
    @ville_depart varchar(32),
    @ville_fin varchar(32)
AS
BEGIN

WITH journey (to_town, steps, distance, trajet)
AS
(SELECT DISTINCT jny_from_town, 0, 0, CAST(jny_from_town AS VARCHAR(MAX))
FROM t_journey
WHERE jny_from_town = @ville_depart
UNION ALL
SELECT jny_to_town, depart.steps + 1, depart.distance + arrive.jny_km, depart.trajet + ', ' + arrive.jny_to_town
FROM t_journey AS arrive
    INNER JOIN journey AS depart
    ON depart.to_town = arrive.jny_from_town)
SELECT TOP 1 * 
FROM journey
WHERE to_town = @ville_fin
ORDER BY distance

[zhick]

Took me a while to figure out this was a recursive with statement, since whatever SQL syntax that is I'm unfamiliar with.

What is it, out of curiosity?

 

Anyway, your problem is that your select basically builds a list of all possible routes, and then selects the shortest one, but the number of possible routes is infinite if there are cycles in your graph.

You have to ensure that you don't visit a node/town you've already visited before, by checking whether it's already been visited == is in your current path (...or trajet in this case).

 

Hope this is enough to point you in the right direction.