SQL tree data struct(7): find the minimal cost route

-- find the minimal cost route

create table flights(departure char(20), destination char(20), cost int)
insert flights
select 'Chicago', 'New York', 10 union
select 'Chicago', 'Milwaukee', 20 union
select 'Denver', 'Chicago', 25 union
select 'Seattle', 'Chicago',30 union
select 'Seattle', 'Denver',40 union
select 'Seattle', 'San Francisco', 50 union
select 'Milwaukee', 'Seattle',11 union    -- cycle 1: to the first source
select 'Milwaukee', 'Michigan', 36 union
select 'Milwaukee', 'Denver', 9 -- cycle 2: to the intermediate source

insert into flights select destination, departure, cost from flights

select * from flights

if object_id('mincostroute') is not null
    drop procedure mincostroute

create procedure mincostroute(@from char(20), @to char(20))
with encryption
declare @depth int, @hasmore int
select @depth = 0, @hasmore = 1

/* can not use table variable, a table-var can not refer : table-var.field */
select top 0 src=replicate('.',20), dst=replicate('.',20), stops=0, route=replicate('.', 4000), ttlcost=0
    into #map from sysfiles1

insert into #map select '', @from, @depth, @from, 0

while @hasmore > 0
    select @depth = @depth + 1

    insert into #map
    select flights.departure, flights.destination, @depth,
        #map.route + '->' + destination, ttlcost+cost
    from #map inner join flights on #map.dst = flights.departure
    where stops = @depth-1 and flights.destination not in (select src from #map)
    set @hasmore = @@rowcount

--select * from #map

--select * from #map where dst = @to
select top 1 route=rtrim(route), ttlcost from #map where dst = @to order by ttlcost asc

drop table #map

exec mincostroute 'Seattle', 'Michigan'
drop table flights
drop proc mincostroute