-- 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
go
create procedure mincostroute(@from char(20), @to char(20))
with encryption
as
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
begin
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
end
--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
go
exec mincostroute 'Seattle', 'Michigan'
drop table flights
drop proc mincostroute
go