Here is an implementation that will work in SQL Se...

2006-12-21T01:33:00.000-05:00

Here is an implementation that will work in SQL Server 2005. If you can think of a solution in SQL Server 2000 or a better solution do let me know.

declare @start char,@end char
set @start = 'c'
set @end = 'd';
with cte as
(
SELECT city1,city2,distance FROM #DISTANCE_TBL
union all
SELECT city2,city1,distance FROM #DISTANCE_TBL
),
cte1 as
(select city1 as start,city2 as dest, cast(city1 + '->' + city2 as varchar(100)) as pth,
distance
from cte where city1 = @start
union all
select a.start,b.city2,cast(pth + '->' + city2 as varchar(100)), a.distance + b.distance from cte1 as a, cte as b
where a.dest = b.city1 and charindex(b.city2 + '->', a.pth) = 0
)
select top 1 * from cte1
where dest = @end
order by distance, len(pth)

This solution is based on pattern matching in a string. I somehow don't like this solution as there are a few situations , though far-fetched, where it might fail.

And, for those who are following these puzzles, please do excuse me for being inactive for the past few days. My job is demanding my blogging time. And the situation is going to continue for a few more days :(

Wish me luck....

Comments on SQL Garbage Collector: A Scenario to Ponder #11

Here is an implementation that will work in SQL Se...