quote:

---

I tried it but its still not working

---

CREATE TABLE Contacts (pFrom VARCHAR(2), pTo VARCHAR(2))

INSERT	Contacts
SELECT	'A', 'B' UNION ALL
SELECT	'B', 'D' UNION ALL
SELECT	'C', 'A' UNION ALL
SELECT	'C', 'E' UNION ALL
SELECT	'G', 'C' UNION ALL
SELECT	'B', 'G' UNION ALL
SELECT	'F', 'D' UNION ALL
SELECT	'E', 'F'


select * from contacts
GO

CREATE FUNCTION	dbo.fnFriendsBetween
(
@From VARCHAR(2),
@To VARCHAR(2)
)
RETURNS @Friends TABLE (Generation INT, p VARCHAR(2))
AS

BEGIN


DECLARE	@Generation INT

SELECT @Generation = 0

INSERT	@Friends
(
Generation,
p
)
SELECT	0,
@From

WHILE @@ROWCOUNT > 0 AND NOT EXISTS (SELECT * FROM @Friends WHERE p = @To)
BEGIN
SELECT @Generation = @Generation + 1

INSERT	@Friends
(
Generation,
p
)
SELECT	@Generation,
pTo
FROM	Contacts
WHERE	pFrom IN (SELECT p FROM @Friends WHERE Generation = @Generation - 1)
AND pTo NOT IN (SELECT p FROM @Friends)	
UNION ALL
SELECT	@Generation,
pFrom
FROM	Contacts
WHERE	pTo IN (SELECT p FROM @Friends WHERE Generation = @Generation - 1)
AND pFrom NOT IN (SELECT p FROM @Friends)
END

RETURN	
END



GO

select *
FROM
     dbo.fnFriendsBetween('A', 'F')

drop table contacts

DECLARE @Stations TABLE (
	[stationID] INT
	, [name] VARCHAR(255)
)

INSERT	@Stations
SELECT 1, 'Glasgow' UNION ALL
SELECT 2, 'Edinburgh' UNION ALL
SELECT 3, 'York' UNION ALL
SELECT 4, 'London' UNION ALL
SELECT 5, 'Aberdeen' UNION ALL
SELECT 6, 'Bjuv' UNION ALL
SELECT 7, 'Båstad'

DECLARE @Links TABLE (
	[fromID] INT
	, [toID] INT
)

INSERT @Links
SELECT 1, 2 UNION ALL -- Glasgow, Edinburgh
SELECT 1, 5 UNION ALL -- Glasgow, Aberdeen
SELECT 1, 3 UNION ALL -- Glasgow, York
SELECT 2, 1 UNION ALL -- Edinburgh, Glasgow
SELECT 2, 3 UNION ALL -- Edinburgh, York
SELECT 2, 7 UNION ALL -- Edinburgh, Båstad
SELECT 3, 2 UNION ALL -- York, Edinburgh
SELECT 3, 4 UNION ALL -- York, London
SELECT 4, 3 UNION ALL -- London, York
SELECT 5, 1 UNION ALL -- Aberdeen, glasgow
SELECT 6, 4 UNION ALL -- Bjuv, London
SELECT 6, 7 UNION ALL -- Bjuv, Båstad
SELECT 7, 2 UNION ALL -- Båstad, Edinburgh
SELECT 7, 6 -- Båstad, Bjuv

/*  -- Node Map (* = Go to)

      A
     *
    /
   *
  G *---* E --* Bd
   \     *       *
    \   /        |
     * *         |
      Y          |
      *          |
      |          |
      *          *
      L *------ Bj

*/
;WITH paths([stationIDs], [pathLength], [hopPath], [fromID], [fromName], [toName])
AS (
	SELECT
		CAST([stationID] AS VARCHAR(MAX))
		, CAST(0 AS INT)
		, CAST([name] AS VARCHAR(MAX))
		, [stationID]
		, CAST([name] AS VARCHAR(MAX))
		, CAST(NULL AS VARCHAR(MAX))
	FROM
		@Stations
	UNION ALL SELECT
		p.[stationIDs] + '/' + CAST(l.[toID] AS VARCHAR(MAX))
		, [pathLength] + 1
		, p.[hopPath] + ' -> ' + s.[name]
		, l.[toID]
		, p.[fromName]
		, CAST(s.[name] AS VARCHAR(MAX))
	FROM
		paths p
		INNER JOIN	@Links l ON l.[fromID] = p.[fromID]
		INNER JOIN	@Stations s ON s.[stationID] = l.[toID]
	WHERE
		'/' + p.[stationIDs] + '/' NOT LIKE '%/' + CAST(l.[toID] AS VARCHAR(MAX)) + '/%'
	)
SELECT
	d.[fromName]
	, d.[toName]
	, d.[hops]
	, d.[hopPath]
FROM
	(
	SELECT
		[fromName]
		, [toName]
		, [pathLength] AS hops
		, [hopPath]
		, ROW_NUMBER() OVER (PARTITION BY fromName, toName ORDER BY pathLength) AS recID
	FROM
		paths
	WHERE
		pathLength >= 1
	) d
WHERE
	[recID] = 1
ORDER BY
	[fromName]
	, [toName]
OPTION (MAXRECURSION 0)