Are you running the queries I provided using the Temp tables as I defined? Or have you modified them to operate off your real tables?
When I run the second query (SELECT * FROM #Hierarchy) this is what I get.
n OriginalPart PartNo PNPrior
1 STC3333 STC8572 STC3333
2 STC3333 STC8572 STC9191
3 STC3333 RTC5756 STC9191
3 STC3333 SFP500160 STC9191
3 STC3333 STC3765 STC9191
3 STC3333 STC9150 STC9191
3 STC3333 STC9191 STC2951
There are 7 rows because of STC3333 (a new PN that I added). With that row included in the sample data, I get this for the last query (the complicated one):
PartNo PartDec Price Supercessions
SFP500160 KIT BRAKE LINING 58.02 STC3333 > STC8572 > STC9191 > STC2951 > STC9150 > STC3765 > RTC5756 > SFP500160
Because of the below SQL assignment to @MyPartNo, if that row doesn't exist in the sample data you'll probably get no rows out of the last query.
DECLARE @N INT = 1
,@MyPartNo VARCHAR(20) = 'STC3333';
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St