Ahh, I feel like a real dumb *** right now ...

It's working much better now, it still struggles on some part numbers though.

If I enter ERR3340, then everything is good:

ERR3340KIT BRAKE LINING 58.02 GFE187 > GFE175 > GFE168 > GFE151 > ETC4953 > ERC8721 > ERR3340

But if I enter GFE168:

ERR3340KIT BRAKE LINING 58.02 GFE168 > ERR3340

Using this code on a live table.

INSERT INTO #Source

SELECT 'ERR3340','KIT BRAKE LINING',58.02;

CREATE TABLE #Hierarchy (Id int NOT NULL IDENTITY(1,1) PRIMARY KEY, [n] nvarchar(max), [OriginalPart] nvarchar(max), [PartNo] nvarchar(max), [PNPrior] nvarchar(max));

DECLARE @N INT = 1

,@MyPartNo VARCHAR(20) = 'ERR3340';

INSERT INTO #Hierarchy([n], [OriginalPart], [PartNo], [PNPrior])

SELECT n=@N, OriginalPart=@MyPartNo, a.Superseeding_Item, Superseeded_Item

FROM dbo.ITEM_SUPERSESSION a

WHERE a.Superseeded_Item = @MyPartNo OR a.Superseeding_Item = @MyPartNo;