id  desc      parent

1   This      null
2   is        1
3   a sample  2
4   table     3

DECLARE @foo TABLE (
	[fooID] INT PRIMARY KEY
	, [fooDesc] VARCHAR(10)
	, [parentID] INT
	)
	
INSERT @foo ([fooId], [fooDesc], [parentID])
      SELECT 1, 'This', NULL
UNION SELECT 2, 'is', 1
UNION SELECT 3, 'a sample', 2
UNION SELECT 4, 'table', 3
-- Another Sentance
UNION SELECT 5, 'I', NULL
UNION SELECT 6, 'Recurse', 5
UNION SELECT 7, 'Therefore', 6
UNION SELECT 8, 'I', 7
UNION SELECT 9, 'Recurse!', 8


DECLARE @startID INT SET @startId = 9

; WITH

	hierarchy AS (
		-- Anchor
		SELECT
			f.[fooID] AS [fooID]
			, CAST(f.[fooDesc] AS VARCHAR(MAX))  AS [DESC_Chain]
			, CAST(f.[fooID] AS VARCHAR(MAX)) AS [ID_Chain]
			, f.[parentID] AS [parentID]
		FROM
			@foo AS f
		WHERE
			f.[fooID] = @startId
	
		-- Recursive
		UNION ALL SELECT
			parent.[fooID] AS [fooID]
			, CAST(child.[DESC_Chain] + ' -> ' + parent.[fooDesc] AS VARCHAR(MAX))
			, CAST(child.[ID_Chain] + ' -> ' + CAST(parent.[fooID] AS VARCHAR(10)) AS VARCHAR(MAX))
			, parent.[parentID]
		FROM
			hierarchy AS child
			JOIN @foo AS parent ON parent.[fooID] = child.[parentID]
		)
	
SELECT * FROM hierarchy WHERE [parentID] IS NULL

fooID       DESC_Chain                                         ID_Chain                       parentID
----------- -------------------------------------------------- ------------------------------ -----------
5           Recurse! -> I -> Therefore -> Recurse -> I         9 -> 8 -> 7 -> 6 -> 5          NULL