SQL and Shortest Path algorithm

Title: There are several points in space, the connection between each point is random, the best algorithm for finding any point (set to a) to another point (set to Z) is at least one of the other points (available SQL database)

Constraint: In a single point you can only find the point directly connected to it

Use: To get to know a person by the quickest speed through the list of Friends (MM/GG)

For example, there are 1,30,3 in the 5 buddy list.

There are 9,5,8 in the 7 buddy list.

There are 7,21,30 in the 10 buddy list.

There are 7,5,30 in the 11 buddy list.

There are 7,30,66 in the 21 buddy list.

There are 21,88,99 in the 30 buddy list.

If 5 to make friends with 7, you can pass the 5-11-7. And the 5-30-21-7 is a long path.

What's the big shrimp you can do in the SQL implementation of this algorithm?

--If you have a two-way connection, try the following statement

DECLARE @t table
(
ID int,
f_id varchar (20)
)
INSERT INTO @t
Select 5, ' 1,7,30,3 ' UNION ALL
Select 7, ' 11,21,9,5,8 ' UNION ALL
Select one, ' 7,21,30 ' UNION ALL
Select, ' 7,11,30,66 ' UNION ALL
Select 30, ' 5,11,21,88,99 '
--select * from @t
DECLARE @start int
DECLARE @end int
DECLARE @node int
DECLARE @count int
DECLARE @result varchar (100)
Set @count =0
Set @start =5
Set @end =11
Set @result = '
DECLARE @tmp table
(
ID int,
f_id varchar (20),
Step int
)
Insert INTO @tmp Select @start, ', @count
While @end isn't in (select ID from @tmp)
Begin
Set @count = @count +1
INSERT INTO @tmp
Select distinct a.id,a.f_id, @count from @t A, @tmp b where charindex (RTrim (b.id), a.f_id) >0 and a.ID don't in (select ID FR Om @tmp)
End
Select @result =rtrim (@count) + ': ' +rtrim (@end)
While @count >1
Begin
Set @count = @count-1
Select top 1 @end =id from @tmp where step= @count and charindex (RTrim (@end), f_id) >0
Select @result =rtrim (@count) + ': ' +rtrim (@end) + '/' + @result
End
Select @result = ' 0: ' +rtrim (@start) + '/' + @result
Select @result

/*

0:5/1:7/2:11

*/