/*
I have a table that is tracing a doubly linked list - like structure.

create table t1 (id int primary key, nextid int, previd int)

with this sample data
insert into t1
select 9, 18, 2 UNION ALL
select 18, 6, 9 UNION ALL
select 11, 2, 7 UNION ALL
select 1, 8, 6 UNION ALL
select 3, 7, 0 UNION ALL
select 6, 1, 18 UNION ALL
select 7, 11, 3 UNION ALL
select 8, 0, 1 UNION ALL
select 2, 9, 11

the first node has previd = 0 and the last node has nextid = 0.

i need a query to show the chain-nodes:

id nextid previd
----------- ----------- -----------
3 7 0
7 11 3
11 2 7
2 9 11
9 18 2
18 6 9
6 1 18
1 8 6
8 0 1
*/

DECLARE @startId INT SET @startId = 3

DECLARE @table1 TABLE (
	[ID] INT PRIMARY KEY
	, [nextID] INT
	, [prevID] INT
	)
INSERT @table1
          SELECT 9, 18, 2
UNION ALL SELECT 18, 6, 9
UNION ALL SELECT 11, 2, 7
UNION ALL SELECT 1, 8, 6
UNION ALL SELECT 3, 7, 0
UNION ALL SELECT 6, 1, 18
UNION ALL SELECT 7, 11, 3
UNION ALL SELECT 8, 0, 1
UNION ALL SELECT 2, 9, 11


; WITH chain (
	[id]
	, [nextId]
	, [prevId]
	, [LEVEL]
	)
AS (
	-- Anchor
	SELECT
		[ID]
		, [nextID]
		, [prevID]
		, 0
	FROM
		@table1
	WHERE
		[ID] = @startID

	-- Recursive
	UNION ALL SELECT
		t.[ID]
		, t.[nextID]
		, t.[prevId]
		, c.[LEVEL] + 1
	FROM
		chain c
		JOIN @table1 t ON t.[ID] = c.[nextID]
	)
SELECT
	[Id]
	, [nextID]
	, [prevID]
FROM
	chain
ORDER BY
	[Level]

/*
I have a table that is tracing a doubly linked list - like structure.

create table t1 (id int primary key, nextid int, previd int)

with this sample data
insert into t1
select 9, 18, 2 UNION ALL
select 18, 6, 9 UNION ALL
select 11, 2, 7 UNION ALL
select 1, 8, 6 UNION ALL
select 3, 7, 0 UNION ALL
select 6, 1, 18 UNION ALL
select 7, 11, 3 UNION ALL
select 8, 0, 1 UNION ALL
select 2, 9, 11

the first node has previd = 0 and the last node has nextid = 0.

i need a query to show the chain-nodes:

id nextid previd
----------- ----------- -----------
3 7 0
7 11 3
11 2 7
2 9 11
9 18 2
18 6 9
6 1 18
1 8 6
8 0 1
*/

DECLARE @startId INT SET @startId = 3

DECLARE @table1 TABLE (
	[ID] INT PRIMARY KEY
	, [nextID] INT
	, [prevID] INT
	)
INSERT @table1
          SELECT 9, 18, 2
UNION ALL SELECT 18, 6, 9
UNION ALL SELECT 11, 2, 7
UNION ALL SELECT 1, 8, 6
UNION ALL SELECT 3, 7, 0
UNION ALL SELECT 6, 1, 18
UNION ALL SELECT 7, 11, 3
UNION ALL SELECT 8, 0, 1
UNION ALL SELECT 2, 9, 11


-- 2000 Version
DECLARE @ins INT
DECLARE @level INT

SET @level = 0

DECLARE @newTable TABLE (
	[id] INT PRIMARY KEY
	, [nextId] INT
	, [prevId] INT
	, [LEVEL] INT
	)

INSERT INTO @newTable ([Id], [nextId], [prevId], [LEVEL])
SELECT [Id], [nextId], [prevID], @level FROM @table1 WHERE [ID] = @startID
SET @ins = @@ROWCOUNT

WHILE @ins > 0 BEGIN
	INSERT INTO @newTable ([Id], [nextId], [prevId], [LEVEL])
	SELECT 
		t.[ID]
		, t.[nextID]
		, t.[prevId]
		, c.[LEVEL] + 1
	FROM
		@newTable c
		JOIN @table1 t ON t.[ID] = c.[nextID]
	WHERE
		c.[level] = @level

	SET @ins = @@ROWCOUNT
	SET @level = @level + 1
END

SELECT * FROM @newTable ORDER BY [Level]