It is already declared in the code I provided. You need to do it before the first SELECT into the temp #Hierarchy table. Let's do this a step at a time (new solution that may be closer to what you need):

First, your sample data with the row I added for STC3333:

CREATE TABLE #Supersessions

(

PartNo VARCHAR(20)

,PNPrior VARCHAR(20)

);

INSERT INTO #Supersessions

SELECT 'RTC5756', 'STC9191'

UNION ALL SELECT 'SFP500160','STC9191'

UNION ALL SELECT 'STC9191','STC2951'

UNION ALL SELECT 'STC3765','STC9191'

UNION ALL SELECT 'STC8572','STC9191'

UNION ALL SELECT 'STC9150','STC9191'

UNION ALL SELECT 'STC8572','STC3333';

CREATE TABLE #Source

(

PartNo VARCHAR(20)

,PartDesc VARCHAR(100)

,Price MONEY

);

INSERT INTO #Source

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

Now the DECLARE (with the new variable @MYPartNo which gets passed in by the UI):

DECLARE @N INT = 1

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

First SELECT to create the #Hierarchy table, plus the WHILE loop to fully populate it:

SELECT n=@N, OriginalPart=@MyPartNo

,a.PartNo, PNPrior

INTO #Hierarchy

FROM #Supersessions a

WHERE a.PartNo = @MyPartNo OR a.PNPrior = @MyPartNo;

WHILE @@ROWCOUNT > 0

BEGIN

SELECT @N = @N + 1;

INSERT INTO #Hierarchy

SELECT @N, OriginalPart, PartNo, PNPrior

FROM

(

SELECT OriginalPart, b.PartNo, b.PNPrior

FROM #Hierarchy a

JOIN #Supersessions b ON a.PNPrior = b.PNPrior

UNION ALL

SELECT OriginalPart, b.PartNo, b.PNPrior

FROM #Hierarchy a

JOIN #Supersessions b ON a.PNPrior = b.PartNo

UNION ALL

SELECT OriginalPart, b.PartNo, b.PNPrior

FROM #Hierarchy a

JOIN #Supersessions b ON a.PartNo = b.PNPrior

UNION ALL

SELECT OriginalPart, b.PartNo, b.PNPrior

FROM #Hierarchy a

JOIN #Supersessions b ON a.PartNo = b.PartNo

EXCEPT

SELECT OriginalPart, PartNo, PNPrior

FROM #Hierarchy

) a;

END

Now we take a look at what's in the #Hierarchy table (so you can see what I did) and then do a SELECT that might be close to what you want:

SELECT * FROM #Hierarchy;

WITH AllParts AS

(

SELECT PN, rn, N, OriginalPart, m

FROM

(

SELECT OriginalPart, PN, rn=ROW_NUMBER() OVER (PARTITION BY PN ORDER BY N), N, m

FROM #Hierarchy a

CROSS APPLY (VALUES (1, PartNo), (0, PNPrior)) b (m, PN)

) b

WHERE rn = 1

)

SELECT b.PartNo, PartDec=MAX(b.PartDesc), Price=MAX(b.Price)

,Supercessions=REPLACE(STUFF(

(

SELECT '-' + PN

FROM AllParts c

ORDER BY N, m, CASE WHEN c.PN = b.PartNo THEN '' ELSE c.PN END DESC

FOR XML PATH('')

), 1, 1, ''), '-', ' > ')

FROM AllParts a

JOIN #Source b ON b.PartNo = a.PN

GROUP BY b.PartNo;

Finally, we drop our temp tables, cleaning up our sandbox.

GO

DROP TABLE #Source;

DROP TABLE #Supersessions;

DROP TABLE #Hierarchy;

That whole sorting thing is pretty problematic. I suspect you won't like the ordering if you happen to pick a PN that's somewhere in the middle of the chain. Like I said, without a date of supersession or something, driving the ordering is going to be a headache.